在Sql Server中如何对多个列进行数据透视 [英] In Sql Server how to Pivot for multiple columns

问题描述

这是我的示例表，我想透视类别列，并将销售，库存和目标作为行获取

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

推荐答案

样本表:

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脚本中:

IN SQL SERVER Script :

  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

这篇关于在Sql Server中如何对多个列进行数据透视的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！