In Sql Server how to Pivot for multiple columns

2019-01-20 17:41发布

This is my sample Table, i want to pivot the category column and get the sales,stock and target as rows

enter image description here

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

enter image description here

4条回答
姐就是有狂的资本
2楼-- · 2019-01-20 17:49

Following should work,

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
查看更多
你好瞎i
3楼-- · 2019-01-20 18:00

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

Like

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

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

查看更多
爱情/是我丢掉的垃圾
4楼-- · 2019-01-20 18:07

Try below solution

-- Applying pivoting on multiple columns
SELECT
*
FROM
(
  SELECT 
   Branch,
   Category,
   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 Category IN ([Panel], [AC], [Ref])
 ) AS pv2

 -- For Target
 PIVOT
 (
   SUM(Target) FOR Category IN ([Panel], [AC], [Ref])
 ) AS pv3
 GO
查看更多
走好不送
5楼-- · 2019-01-20 18:11

Sample Table :

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)
;

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
查看更多
登录 后发表回答