在 Sql Server 中如何透视多列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/38067490/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
In Sql Server how to Pivot for multiple columns
提问by Saad
This is my sample Table, i want to pivot the category column and get the sales,stock and target as rows
这是我的示例表，我想旋转类别列并将销售额、库存和目标作为行
I want the sample output in this form as shown in the below wherein the categories are in place of columns and columns in place of row
我想要这种形式的示例输出，如下所示，其中类别代替列和列代替行
回答by mohan111
Sample Table :
样品表：
DECLARE @Table1 TABLE
(Branch varchar(9), Category varchar(9), Sales INT,Stock INT,Target INT)
;
INSERT INTO @Table1
(Branch, Category, Sales, Stock,Target)
VALUES
( 'mumbai', 'panel', 10,4,15),
( 'mumbai', 'AC', 11,7,14),
( 'mumbai', 'Ref', 7,2,10),
( 'Delhi', 'panel',20,4,17),
( 'Delhi', 'AC', 5,2,12),
( 'Delhi', 'Ref', 10,12,22)
;
IN SQL SERVER Script :
在 SQL 服务器脚本中：
Select BRANCH,COL,[panel],[AC],[Ref] from (
select Branch,Category,COL,VAL from @Table1
CROSS APPLY (VALUES ('Sales',Sales),
('Stock',Stock),
('Target',Target))CS (COL,VAL))T
PIVOT (MAX(VAL) FOR Category IN ([panel],[AC],[Ref]))PVT
ORDER BY Branch DESC
回答by Ankur Gupta
You gotta change the name of columns for next Pivot Statement.
您必须更改下一个 Pivot 语句的列名称。
Like
喜欢
SELECT
*
FROM
(
SELECT
Branch,
Category,
Category+'1' As Category1,
Category+'2' As Category2,
Sales,
Stock,
Target
FROM TblPivot
) AS P
-- For Sales
PIVOT
(
SUM(Sales) FOR Category IN ([Panel], [AC], [Ref])
) AS pv1
-- For Stock
PIVOT
(
SUM(Stock) FOR Category1 IN ([Panel1], [AC1], [Ref1])
) AS pv2
-- For Target
PIVOT
(
SUM(Target) FOR Category2 IN ([Panel2], [AC2], [Ref2])
) AS pv3
GO
You are ready to go now....
你现在准备好了......
You can use aggregate of pv3 to sum and group by the column you need.
您可以使用 pv3 的聚合按您需要的列求和和分组。
回答by Vignesh Kumar A
Try below solution
尝试以下解决方案
-- Applying pivoting on multiple columns
SELECT
*
FROM
(
SELECT
Category,
Sales,
FROM TblPivot
) AS P
-- For Sales
PIVOT
(
SUM(Sales) FOR Category IN ([Panel], [AC], [Ref])
) AS pv1
union all
-- For Stock
SELECT
*
FROM
(
SELECT
Category,
Stock,
FROM TblPivot
) AS P
PIVOT
(
SUM(Stock) FOR Category IN ([Panel], [AC], [Ref])
) AS pv2
union all
-- For Target
SELECT
*
FROM
(
SELECT
Category,
Target,
FROM TblPivot
) AS P
PIVOT
(
SUM(Target) FOR Category IN ([Panel], [AC], [Ref])
) AS pv3
GO
回答by suraj thakur
Following should work,
以下应该工作，
select * FROM
(
SELECT
Branch,
Category,
Sales,
Stock,
Target
FROM Table1
) AS P
unpivot
(
[Value] FOR [OutPut] IN (sales,stock,[target])
)unpvt
pivot
(
max([Value]) for Category in (Panel,AC,Ref)
)pvt
order by Branch Desc
