你需要为下一个数据透视表更改列名。
例如:
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
现在,您已经准备就绪了....
您可以使用pv3的聚合功能对需要的列进行求和和分组。
示例表格:
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)
;
在 SQL Server 脚本中:
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
-- 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
(这不是提问者的要求)
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);
SELECT
Branch,
SUM(Panel) As PanelSales,SUM([AC]) As ACSales,SUM([Ref]) As RefSales,
SUM(Panel1) As PanelStock,SUM([AC1]) As ACStock,SUM([Ref1]) As RefStock,
SUM(Panel2) As PanelTarget,SUM([AC2]) As ACTarget,SUM([Ref2]) As RefTarget
FROM
(
SELECT
Branch,
Category,
Category+'1' As Category1,
Category+'2' As Category2,
Sales,
Stock,
Target
FROM @Table1
) 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
Group BY Branch
GO
以下代码应该可以正常工作:
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
UNPIVOT
将数据转换为平面表,然后再使用 PIVOT
将类别转换为列:SELECT *
FROM SampleTable
UNPIVOT (Amount FOR Output IN (Sales, Stock, Target)) upvt
PIVOT (SUM(Amount) FOR Category IN (Panel, AC, Ref)) pvt
ORDER BY Branch, Output;