sql - PIVOT values from two columns to multiple columns -
table: sample
id day status ms ---------------------------- 1 1 0 10 1 2 0 20 1 3 1 15 2 3 1 3 2 30 0 5 2 31 0 6 expected result:
id day1 day2 day3....day30 day31 status1 status2 status3...status30 status31 --------------------------------------------------------------------------------------- 1 10 20 15 null null 0 0 1 null null 2 null null 3 5 6 null null 1 0 0 i want ms , status value each day 1 31 each id.
i have used pivot below result.
result:
id day1 day2 day3....day30 day31 ------------------------------------- 1 10 20 15 null null 2 null null 3 5 6 query:
select id ,[1] day1 ,[2] day2 ,[3] day3 . . . ,[30] day30 ,[31] day31 ( select id ,[day] ,ms sample ) pivot ( min(ms) [day] in([1],[2],[3],...[30],[31]) ) pvttable how can merge status column result?.
try this. use pivot transpose status column. use aggregate (max or min) in select column list group id result.
create table #est (id int,[day] int,[status] int,ms int) insert #est values (1,1,0,10),(1,2,0,20),(1,3,1,15 ), (2,3,1,3),(2,30,0,5),(2,31,0,6) select id, max([day1]) [day1], max([day2]) [day2], max([day3]) [day3], max([day30]) [day30], max([day31]) [day31], max([status1]) [status1], max([status2]) [status2], max([status3]) [status3], max([status30])[status30], max([status31])[status31] (select id, 'status' + convert(varchar(30), day) col_stat, 'day' + convert(varchar(30), day) col_day, [status], ms #est) pivot (min([ms]) col_day in([day1],[day2],[day3],[day30],[day31])) piv pivot (min([status]) col_stat in ([status1],[status2],[status3],[status30],[status31])) piv1 group id
Comments
Post a Comment