SQL Pivot rows into columns

Question

I have the following table, and am trying to pivot it so that a new column is created for each item per user, but all users are grouped into one row e.g. if the most items a user has is 4, i would like 4 item columns.

I know that I need to use dynamicSQL and possibly the COALESCE function too, but this doesn't seem to produce the required result.

I need to pivot this:

UserName   UserId   ItemiD  ItemName
-----      ----     ------  --------
Bob         445       46     Hat
Bob         445       138    Tshirt
Rachel      512       46     Hat
Rachel      512       59     Mug
Rachel      512       138    Tshirt
Simon       771       46     Hat
Simon       771       400    MouseMat

Into this:

UserName   UserId   Item1iD  Item1Name   Item2iD  Item2Name Item3iD  Item3Name
-----      ----     ------    --------   -------  --------  -------  --------
Bob         445       46        Hat       138      Tshirt     NULL      NULL
Rachel      512       46        Hat        59        Mug      138      Tshirt
Simon       771       46        Hat       400     MouseMat    NULL      NULL

Many thanks

Have you tried the PIVOT function in SQL? https://learn.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-server-ver15 — Plato77, Jan 10 '20 at 16:04
Hi Plato, yes, i couldn't get the desired result. Will give it another go. Thanks. — Sam, Jan 10 '20 at 16:08
This previous answer may help: https://stackoverflow.com/questions/10428993/understanding-pivot-function-in-t-sql — Plato77, Jan 10 '20 at 16:10

score 1 · Answer 1 · answered Jan 10 '20 at 16:08

One way how you could do it .... tho it ain't so beautiful ....

Data

If (OBJECT_ID('tempdb..#Test') IS NOT NULL) DROP TABLE #Test

create table #Test (UserName nvarchar(20), UserId int, ItemId int, ItemName nvarchar(20));

insert into #Test (UserName, UserId, ItemId, ItemName) 
values
('Bob', 445, 46, 'Hat'),
('Bob', 445, 138, 'Tshirt'),
('Rachel', 512, 46, 'Hat'),
('Rachel', 512, 59, 'Mug'),
('Rachel', 512, 138, 'Tshirt'),
('Simon', 771, 46, 'Hat'),
('Simon', 771, 400, 'MouseMat')

And here is query .... give it a shot and let us know if it helps you

Select UserName,
       UserId,
       MAX([11]) as "Item1",
       MAX([21]) as "ItemName1",
       MAX([12]) as "Item2",
       MAX([22]) as "ItemName2",
       MAX([13]) as "Item3",
       MAX([23]) as "ItemName3"
FROM  (Select UserName,
              UserId,
              ItemId,
              ItemName,
              Concat('1', Row_Number() over (partition by UserId order by UserId)) RN1,
              Concat('2', Row_Number() over (partition by UserId order by UserId)) RN2
       From #Test) as SRC
PIVOT ( MAX(ItemId) FOR RN1 IN ([11], [12], [13], [14]) ) as piv1
PIVOT ( MAX(ItemName) FOR RN2 IN ([21], [22], [23], [24])) as piv2
GROUP BY UserName,  UserId

Hi. Thanks for the answer. Technically this works, but I can't really hardcode the values as there are thousands and they are constantly being updated. — Sam, Jan 10 '20 at 16:10
Nothing is hard coded in here ... you just pivot up to 4 items per user and that is all, well except column count tho — Veljko89, Jan 10 '20 at 16:11

SQL Pivot rows into columns

1 Answers1