SELECT *
FROM
(
SELECT Company, State, Staff
FROM YourCompanyStaffTable
WHERE State IN ('Illinois', 'Texas', 'Tennessee', 'Missouri', 'Kansas', 'Indiana')
) src
PIVOT (
SUM(Staff)
FOR State IN (
'Illinois' as Illinois,
'Texas' as Texas,
'Tennessee' as Tennessee,
'Missouri' as Missouri,
'Kansas' as Kansas,
'Indiana' as Indiana
)
) pvt
ORDER BY Company
select company, sum(case when state='Illinois' then staff else 0 end) as Illinois,
sum(case when state='Texas' then staff else 0 end) as Texas,
sum(case when state='Tennessee' then staff else 0 end) as Tennessee,
sum(case when state='Missouri' then staff else 0 end) as Missouri,
sum(case when state='Kansas' then staff else 0 end) as kansas,
sum(case when state='Indiana' then staff else 0 end) as Indiana from t
group by company
2 回答
在枢中,您可以从源 sub-query 开始。
然后,您可以定义要聚合另一个字段中哪些标题的字段。
在此查询中,新的列名称是从“State”列生成的。
请注意,在源查询中,这些名称也有限制。
这只是出于效率原因。 (从表中提取的数据较少)
并且它将按照 PIVOT 声明中未使用的源字段对结果进行分组。
在这种情况下,它会自动在“公司”列上进行分组。
因此,它为每个“公司”的每个“州”总计“工作人员”。
用例何时