I have a table like this

I want the output in this format. I want to bring year as columns and display the prices for those years.

Important Note: In output, all years have price and other data. In this example, it would be long, so I have kept only few rows. But in my database, basically all years will have data. There will be price for each year for each category, type and Item

Some important things to note:

• This table has over 50,000 records
• There are maximum 2 possible value for Category i.e Cloth and Laptop. Similarly 2 for Type like Small and Large. And for item also 2 i.e Red and Blue. But for Year it can be 2000 to 2021.

How can I select the records and display the result in this way in MS-SQL ?

CodePudding user response：

I'd generally advise against that kind of structure, but if that is a specific requirement, you could find a scalable solution with PIVOT (Microsoft documentation) and a stored procedure with variables (fully dynamic example).

If you're find hand-coding these, a CASE statement would work, where you group by Category, ID and Type and then aggregate as:

CASE(WHEN Year = 2012 THEN Price END) AS `2012`
CASE(WHEN Year = 2011 THEN Price END) AS `2011`
Etc.

CodePudding user response：

You can try to use condition aggregate function to make the pivot

;WITH CTE AS (
    SELECT Category,
           Type,
           Item,
           MAX(CASE WHEN Year = 2010 THEN Price END) '2010',
           MAX(CASE WHEN Year = 2011 THEN Price END) '2011',
           MAX(CASE WHEN Year = 2012 THEN Price END) '2012'
    FROM T
    GROUP BY Category,Type,Item
)
SELECT ROW_NUMBER() OVER(ORDER BY Category) newID,*
FROM T 

EDIT

if there are a lot of rows need to do you might try to use dynamic pivot.

DECLARE @sql  nvarchar(max) = N'',
        @s1  nvarchar(max)  = '';
        
;WITH CTE AS (
    SELECT MIN(Year) s_y,MIN(Year) s_e
    FROM T
    UNION ALL
    SELECT s_y   1 , s_e
    FROM CTE 
    WHERE s_y   1 <= s_e
)
SELECT @s1  = CONCAT('MAX(CASE WHEN Year = ',s_y,' THEN Price END) [',s_y,'],') 
FROM CTE 

SET @sql = N'SELECT Category,
       Type,'   @s1   N'Item FROM T GROUP BY Category,Type,Item'
          
PRINT @sql;
EXEC sys.sp_executesql @sql;