SQL Server - 创建枢轴的简单方法 [英] SQL Server - simple way to create pivot
本文介绍了SQL Server - 创建枢轴的简单方法的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
有谁知道一种简单的方法来创建这个结果的枢轴:
Does anybody knows a simple way to create a pivot of this result:
[0-1] [1-2] [2-3] [3-4]
12 45 29 5
此结果是通过查询创建的:
This result is created with query:
SELECT [0-1] = SUM(CASE WHEN AGE >= 0 AND AGE <= 1 THEN 1 ELSE 0 END),
[1-2] = SUM(CASE WHEN AGE > 1 AND AGE <= 2 THEN 1 ELSE 0 END),
[2-3] = SUM(CASE WHEN AGE > 2 AND AGE <= 3 THEN 1 ELSE 0 END)
FROM dbo.Persons
我想将列值显示为行,即输出应如下所示:
I want to show the column values as rows i.e the output should like this:
[0-1] 12
[1-2] 45
[2-3] 29
[3-4] 5
也欢迎链接到文章/博客.请提出一个简单易行的解决方案.
Links to articles/blogs are also welcomed. Please suggest a simple and easy to follow solution.
推荐答案
这里是一种使用cross join
和聚合的方法:
Here is a way that uses cross join
and aggregation:
select ages.minage, ages.maxage,
sum(case when age > ages.minage and age <= ages.maxage then 1 else 0 end) as cnt
from dbo.Persons cross join
(select -1 as minage, 1 as maxage union all
select 1, 2 union all
select 2, 3
) as ages
group by ages.minage, ages.maxage
我喜欢这种方法,因为它很容易添加新的年龄范围.请注意,这也包括范围.
I like this method because it is easy to add new age ranges. Note this also includes the ranges.
这篇关于SQL Server - 创建枢轴的简单方法的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文