question

Lukasz8519 avatar image
Lukasz8519 asked

pivot using 2 columns t-sql

hi everyone,

i have case that i have to pivot using two columns,

DECLARE @T TABLE ( sect_id int ,level_id int , time_perd_id INT NOT NULL , val DECIMAL(6,4) NOT NULL ,val_style decimal(3)NOT NULL ) insert @t values (2,1,15135,33.22,110), (2,1,15136,35.11,112), (2,1,15137,37.31,110), (2,1,15138,37.45,111), (2,1,20522,39.21,110), (2,2,15135,32.22,112), (2,2,15136,36.11,113), (2,2,15137,30.31,111), (2,2,15138,34.45,111), (2,2,20522,33.21,112), (2,3,15135,32.22,110), (2,3,15136,36.11,114), (2,3,15137,30.31,113), (2,3,15138,34.45,112), (2,3,20522,33.21,114) --

--SELECT * FROM @t

;WITH CTE AS ( SELECT sect_id , level_id , time_perd_id , val , val_style FROM @T )

SELECT sect_id , level_id ,MAX([15135]) as val1 ,MAX([110]) as val_style_1 ,MAX([15136]) as val2 ,MAX([111]) as val_style_2 ,MAX([15137]) as val3 ,MAX([112]) as val_style_3 ,MAX([15138]) as val4 ,MAX([114]) as val_style_4 ,MAX([20522]) as val5 FROM CTE

PIVOT (SUM(val) FOR time_perd_id IN ([15135],[15136],[15137],[15138],[20522])) AS pvt1

PIVOT (MAX(val_style) FOR val_style IN ([110],[111],[112],[113],[114])) pvt2

group by sect_id , level_id

i need to receive

sect_id,level_id, val_1, val_1_style, val_2,val_2_style etc.

i don't know what i'm doing wrong because i need move val_style into column which will be val_1_style etc. maybe my second pivot is not correct

t-sqlpivot
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Lukasz8519 avatar image
Lukasz8519 answered

i resolved this case, closed this topic

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.