在 Sql Server 中如何透视多个列

2022-01-22 00:00:00 pivot sql 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

推荐答案

您必须更改下一个 Pivot 语句的列名.

You gotta change the name of columns for next Pivot Statement.

喜欢

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....

您可以使用 pv3 的聚合按您需要的列进行汇总和分组.

You can use aggregate of pv3 to sum and group by the column you need.

相关文章