将行转置/旋转到列和总和 [英] Transpose/Pivot Rows to Columns and Sum

问题描述

这是我的查询

SELECT * 
FROM requirementRange

PeakRange，DaysOfReq列是nvarchar数据类型，Total列是INT数据类型

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

表如下所示

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

预期结果

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.

推荐答案

您应该可以使用类似于以下内容的东西:

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;

请参见带有演示的SQL提琴

这篇关于将行转置/旋转到列和总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！