在Sql Server中如何对多个列进行数据透视 [英] In Sql Server how to Pivot for multiple columns
本文介绍了在Sql Server中如何对多个列进行数据透视的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
这是我的示例表,我想透视类别列,并将销售,库存和目标作为行获取
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屋!
查看全文