sql server - how to set rows as columns?

Question

hopefully someone can help me with this. I have a table that looks like the following

ID  | Column | New Value | ModifiedDate

223 | Num    | 98        | 03/01/2018
223 | Country| IRE       | 03/01/2018
223 | User   | Bob       | 03/01/2018
222 | User   | Lily      | 01/01/2018
222 | Num    | 72        | 01/01/2018
222 | Country| UK        | 01/01/2018
222 | User   | Wendy     | 02/01/2018
222 | Num    | 22        | 02/01/2018
222 | Country| UK        | 02/01/2018

I want a query that somehow merges all this into rows based on ID and modified date. So for the above table, I want the following output

ID  | Num | Country | User | Modified Date
222 |  72 |   UK    | Lily | 01/01/2018
222 |  22 |   UK    |Wendy | 02/01/2018
223 |  98 |  IRE    | Bob  | 03/01/2018

Can someone help with this? I have looked for similar problems on here but can't find anything that actually works when I use it against my table?

Also, the number of IDs can change so it would have to be dynamic?

Many thanks for any help!!

Possible duplicate of [Convert Rows to columns using 'Pivot' in SQL Server](https://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server) — Chris Pickford, Jan 19 '18 at 11:51
Why are there *two* rows for ID? Converting rows to columns is one thing. Using one of the pivoted values as a key is another. Why is `72` returned for Lily instead of 22 ? You'll have to explain the logic for grouping the rows — Panagiotis Kanavos, Jan 19 '18 at 11:56
because the date this column was changed is different. 72 on 01/01/2018 and 22 on 02/01/2018 — Jess8766, Jan 19 '18 at 11:58
_can't find anything that actually works_.. I suggest you find something that almost works and explain what you did and what went wrong — Nick.Mc, Jan 19 '18 at 13:58

score 0 · Answer 1 · answered Jan 19 '18 at 12:00

0

As simple as:

SELECT * 
FROM TableName
   PIVOT ( MAX(NewValue)
           FOR [Column]
           IN ([Num] , [Country] , [User])
         )p

answered Jan 19 '18 at 12:00

M.Ali

67,945
13
101
127

score 0 · Answer 2 · answered Jan 19 '18 at 13:26

Old-School pivoting works also

select 
ID, 
max(case when [Column] = 'Num' then [New Value] end) as Num,
max(case when [Column] = 'Country' then [New Value] end) as Country,
max(case when [Column] = 'User' then [New Value] end) as [User],
ModifiedDate
from YourTable 
group by ID, ModifiedDate
order by ID, ModifiedDate;

sql server - how to set rows as columns?

2 Answers2