sql - Transpose/Pivot Rows to Columns and Sum -
here query
select * requirementrange
peakrange
,daysofreq
columns nvarchar datatype , total
int datatype
table below
peakrange daysofreq total 1 - 3.99 >2 days 2 9.01+ day 2 3 1 - 3.99 day 0 1 4 - 5.99 day 0 1 6 - 8.99 day 2 2 9 day 0 1 9.01+ day 0 1
expected result
peakrange day 0 day 1 day 2 >2 days total 1 - 3.99 1 0 0 2 3 4 - 5.99 1 0 0 0 1 6 - 8.99 0 0 2 0 2 9 1 0 0 0 1 9.01+ 1 0 3 0 4
here need output converted rows columns finding total , placing in last column each range.
you should able use similar following:
select peakrange, coalesce([day 0], 0) [day 0], coalesce([day 1], 0) [day 1], coalesce([day 2], 0) [day 2], coalesce([>2 days], 0) [>2 days], peak_total ( select peakrange, daysofreq, total, sum(total) over(partition peakrange) peak_total requirementrange ) d pivot ( sum(total) daysofreq in ([day 0], [day 1], [day 2], [>2 days]) ) piv order peakrange;
see sql fiddle demo
Comments
Post a Comment