-2

I have a table like this

Id Year Category Type Item Price
1 2010 Cloth Small Red 25
2 2010 Cloth Large Blue 30
3 2010 Laptop Small Blue 15
4 2011 Cloth Small Red 22
5 2011 Cloth Large Blue 28
6 2011 Laptop Large Red 33
7 2012 Laptop Small Blue 35

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

enter image description here

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 ?

Joel Coehoorn
  • 399,467
  • 113
  • 570
  • 794
Vim
  • 161
  • 2
  • 3
  • 11
  • See how I changed your sample data? Use the edit button to see what that looks like when making a post, and then do the same thing for the second sample. If you post images of sample data here, the expectation is the question will get downvoted, and you'll be less likely to get a prompt answer. – Joel Coehoorn Feb 01 '22 at 15:18
  • Does it asnwer your question [Efficiently convert rows to columns in sql server](https://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server/15745076#15745076) – Serg Feb 01 '22 at 15:36

2 Answers2

0

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;
D-Shih
  • 44,943
  • 6
  • 31
  • 51
  • Thanks for your help. Actually my table has thousands of records. So, is there any way to display years without typing each year like in your solution ? – Vim Feb 01 '22 at 15:09
  • @Vim The SQL language has a strict requirement to know the number of columns in the results ahead of time, before building the execution plan _or looking at any data_. If you have to look into the data to know what years you have, and can't know this in advance, then you'll have to do it over multiple steps: 1) write a query to get your years; 2) Use the results from step 1 to build a new query that specifies each year; and 3) Run the query from step 2. Commonly instead this kind of **pivot** is handled in the client code or reporting tool instead of in SQL. – Joel Coehoorn Feb 01 '22 at 15:23
  • @Vim I edit my answer from your edit question you are looking for dynamic pivot. – D-Shih Feb 01 '22 at 15:25
0

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.
darren
  • 1
  • 1