在 SQL Server 中如何透视多个列

Question

这是我的示例表，我想旋转类别列并将销售、库存和目标作为行

我想要这种形式的示例输出，如下所示，其中类别代替列，列代替行

原文由 Saad 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 1

来自 答案 1 和 答案 2

（这不是OP的要求）

 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

原文由 Palanikumar 发布，翻译遵循 CC BY-SA 4.0 许可协议