CODEWITHSUNDEEP
sqlsql-servert-sqlpivotunpivot
user2827224
user2827224

Reputation: 61

SQL convert columns to rows

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.

Upvotes: 1

Views: 995

Answers (1)

Serkan Arslan
Serkan Arslan

Reputation: 13393

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

Upvotes: 2

Related Questions