Transpose/Pivot Rows to Columns and Sum

问题:

Here is my query

SELECT * 
FROM requirementRange

PeakRange,DaysOfReq columns are nvarchar datatype and Total is INT datatype

Table is Like 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 I need the output converted from rows to columns as well as finding the total and placing in the last column for each range.

回答1:

You should be able to use something similar to the 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
from
(
  select peakrange, daysofreq, total,
    sum(total) over(partition by PeakRange) peak_Total
  from requirementRange
) d
pivot
(
  sum(total)
  for daysofreq in ([Day 0], [Day 1], [Day 2],
                    [>2 Days])
) piv
order by peakrange;

See SQL Fiddle with Demo