SQL Transpose multiple columns into rows

Question

Hello I am new to SQL and would really appreciate some help with transposing a table as follows. Looking at similar posts it seems that this may be accomplished with pivot/unpivot, but I am not sure since the examples I read about have much fewer columns. Any suggestions would be great!

Table.

CATEGORY Value1 Value2  Value3  Value4  ... Value15

Hot  18 17  9   17  ... 18

Warm     5  3   0   1   ... 3

Cold     20 2   1   2   ... 2

Desired Result.

CATEGORY Hot    Warm    Cold

Value1   18 5   20

Value2   17 3   2

Value3   9  0   1

Value4   17 1   2

…    …  …   …

Value15  18 3   2

Ultimate answer? load it all into a recursive array and the replay it with the lower levels in reverse. — DevlshOne, Oct 15 '13 at 15:14
http://stackoverflow.com/questions/13372276/simple-way-to-transpose-columns-and-rows-in-sql — Anup Agrawal, Oct 15 '13 at 15:46
http://stackoverflow.com/questions/15297809/sql-transpose-full-table — Anup Agrawal, Oct 15 '13 at 15:46

score 1 · Answer 1 · answered Oct 15 '13 at 16:01

1

select *
from tbl
unpivot (value for name in (Value1, Value2,  Value3,  Value4,  ... Value15)) unpiv
pivot (max(value) for category in ([hot],[warm],[cold])) piv

answered Oct 15 '13 at 16:01

Hogan

69,564
10
76
117

score 0 · Answer 2 · answered Oct 15 '13 at 16:15

The following is a simple code i tried out. Just run and see if it meets your requirement!!


declare @Tbl1 table(Category Varchar(10), Value1 Int, Value2 Int, Value3 Int, Value4 Int, Value5 Int)

insert into @Tbl1(Category, Value1, Value2, Value3, Value4, Value5)
    Values ('Hot', 12, 23, 43, 5, 6)
    insert into @Tbl1(Category, Value1, Value2, Value3, Value4, Value5)
    Values ('Warm', 41, 28, 4, 45, 16)
    insert into @Tbl1(Category, Value1, Value2, Value3, Value4, Value5)
    Values ('Cold', 1, 3, 543, 15, 26)

    select * from @Tbl1

 Declare @TblUP Table(Category Varchar(100), Value Int, [Types] Varchar(10))

 Insert into @TblUP(Category, Value, Types) 
 select * from @Tbl1 unpivot ([values] for Types in (Value1, Value2, Value3, Value4, Value5)) as UNPIV
 select * from @TblUP

 Select Types, Hot, Warm, Cold
 From
    (Select Types, Category, Value from @TblUP) as TUP
    PIVOT (SUM(Value) for [Category] in ([Hot], [Warm], [Cold]))
    as PT

SQL Transpose multiple columns into rows

2 Answers2