SQL convert columns to rows

Question

I've been reading a lot about SQL Pivots and Unpivots, but I'm not sure how to tackle the problem that I currently have.

Basically, this is my data set.

ItemNumber | Category 1 | Category 2 | Category 3
00001           Books      Children      Beginner
00002           Games      Adults        Advanced

How can I rotate this table to get this result?

ItemNumber  |   00001       |    00002
Category 1     Books            Games   
Category 2     Children         Adults
Category 3     Beginner        Advanced

I was reading about Unpivoting but it looks like Unpivoting doesn't work in my case.

Would appreciate some help.

Possible duplicate of [SQL Server dynamic PIVOT query?](https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query) — RoMEoMusTDiE, Jan 22 '18 at 19:49

score 2 · Answer 1 · answered Jan 22 '18 at 19:51

You can use this.

DECLARE @T TABLE ( ItemNumber VARCHAR(10), [Category 1] VARCHAR(10), [Category 2] VARCHAR(10), [Category 3] VARCHAR(10))
INSERT INTO @T VALUES
('00001','Books','Children','Beginner'),
('00002','Games','Adults','Advanced')

SELECT Col ItemNumber,  [00001], [00002] FROM @T 
    UNPIVOT( Val FOR Col IN ([Category 1],[Category 2],[Category 3])) UNPVT
    PIVOT (MAX(Val) FOR ItemNumber IN ([00001],[00002])) PVT

Result:

ItemNumber               00001      00002
------------------------ ---------- ----------
Category 1               Books      Games
Category 2               Children   Adults
Category 3               Beginner   Advanced

SQL convert columns to rows

1 Answers1