如何在SQL Server中根据条件将行转置为列？

Question

我有一个表，其中包含这四列-土壤类型、总小时数、体积和月份-年。

我已经提出了两个查询，以基于月-年条件根据土壤类型生成总小时数和总交易量。

下面是我的输出：

按土壤类型划分的总小时数：

SELECT 
    [Soil Type], [Month/Year],
    SUM([Total Hour]) AS 'Total_Hour' 
FROM 
    OVERALL_SUMMARY_ORIGINAL 
WHERE 
    [Month/Year] = '2019-10-01' 
GROUP BY 
    [Soil Type], [Month/Year] 
ORDER BY 
    [Soil Type]

输出：

​

​

土壤类型的总蓄积量：

SELECT 
    [Soil Type], [Month/Year],
    SUM([Volume]) AS 'Total Volume' 
FROM 
    OVERALL_SUMMARY_ORIGINAL 
WHERE 
    [Month/Year] = '2019-10-01' 
GROUP BY 
    [Soil Type], [Month/Year] 
ORDER BY 
    [Soil Type]

输出：

​

​

我一直在尝试弄清楚如何组合这些查询，以便SQL语句首先检查该土壤类型的总体积是否> 10000，如果满足条件，它将用该土壤类型的总体积除以总小时数。然后，SQL应将土壤类型列转置为行，如下所示：

​

​

我尝试使用SQL PIVOT函数，但无法生成所需的上述输出。有人能帮上忙吗？谢谢

Answer 1

如果我正确理解了您的问题，请尝试以下操作：

declare @product table(
 [Soil Type] varchar(50),
 [Month/Year] smalldatetime,
 [Total Hour] int,
 [Volume] int
)

insert into @product values ('Clay', '20190101', 2, 20)
insert into @product values ('Clay', '20190101', 3, 10)
insert into @product values ('Sandy', '20190101', 4, 10)
insert into @product values ('Sandy', '20190201', 2, 25)
insert into @product values ('Clay', '20190301', 8, 40)
insert into @product values ('Mixed', '20190101', 2, 25)
insert into @product values ('Mixed', '20190102', 1, 20)
insert into @product values ('Sand', '20190101', 3, 30)
insert into @product values ('Sand', '20190101', 1, 2)
insert into @product values ('Clay', '20190101', 1, 15)


Select [date], Clay, Mixed, Sand, Sandy
FROM(
SELECT 
    [Soil Type], [Month/Year] as [date],
    SUM([Volume]) / SUM([Total Hour]) as _data
FROM 
   @product
GROUP BY 
    [Soil Type], [Month/Year]
HAVING SUM([Volume])>30) x
PIVOT (
  SUM(_data) FOR [Soil Type] IN (Clay, Mixed, Sand, Sandy)
) pvt