如何在多列上进行透视 [英] how to do pivot on multiple columns
本文介绍了如何在多列上进行透视的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
您好我有样本数据
Declare @table table
(Name Varchar(10),
Cnt INT,
Vol INT,
Descc VARCHAR(10))
INSERT INTO @table(Name,cnt,vol,Descc)values ('Mohan',21,8,'Fed')
INSERT INTO @table(Name,cnt,vol,Descc)values ('Mohan',1,391,'Fed:::')
数据:
Name Cnt Vol Descc
Mohan 21 8 Fed
Mohan 1 391 Fed:::
如何获得这样的输出
Name Cnt1 Vol1 Descc1 cnt2 vol2 Descc2
Mohan 21 8 Fed 1 391 Fed::
我遵循的脚本:
Select [1],[2] from (
select NAme,Cnt,vol,DESCc,ROW_NUMBER()OVER(PARTITION BY ID ORDER BY (SELECT NULL))P,'P'+CAST(ROW_NUMBER()OVER(PARTITION BY ID ORDER BY (SELECT NULL))AS VARCHAR)PP from @table )T
PIVOT (MAX(ID) FOR P IN ([1],[2])) AS P
PIVOT (MAX(ID) FOR PP IN ([P1],[P2])) AS P
推荐答案
声明@table表
(名称Varchar(10),
Cnt INT,
Vol INT,
Descc VARCHAR(10))
INSERT INTO @table(姓名,cnt,vol,Descc)值('Mohan',21, 8,'美联储')
INSERT INTO @table(姓名,cnt,vol,Descc)值('Mohan',1,391,'美联储:::')
SELECT name,
MAX(CASE WHEN = 1 THEN cnt END)cnt1,
MAX(情况下,当rn = 1 THEN vol END)vol1,
MAX(情况下,当rn = 1那么Descc END)Descc1,
MAX(CASE WHEN = 2那么cnt END)cnt2,
MAX(当rn = 2 THEN vol END时的情况)vol2,
MAX(情况下,当rn = 2那么Descc END)Descc2
FROM(
$
SELECT Name,Cnt,Vol,Descc,
ROW_NUMBER()OVER(按名称排序分组) BY(SELECT 0))来自@table
)AS Der GROUP BY名称
Declare @table table
(Name Varchar(10),
Cnt INT,
Vol INT,
Descc VARCHAR(10))
INSERT INTO @table(Name,cnt,vol,Descc)values ('Mohan',21,8,'Fed')
INSERT INTO @table(Name,cnt,vol,Descc)values ('Mohan',1,391,'Fed:::')
SELECT name,
MAX(CASE WHEN rn=1 THEN cnt END ) cnt1,
MAX(CASE WHEN rn=1 THEN vol END ) vol1,
MAX(CASE WHEN rn=1 THEN Descc END ) Descc1,
MAX(CASE WHEN rn=2 THEN cnt END ) cnt2,
MAX(CASE WHEN rn=2 THEN vol END ) vol2,
MAX(CASE WHEN rn=2 THEN Descc END ) Descc2
FROM(
SELECT Name,Cnt,Vol,Descc,
ROW_NUMBER() OVER (PARTITION BY name ORDER BY (SELECT 0)) rn FROM @table
) AS Der GROUP BY name
这篇关于如何在多列上进行透视的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文