In SQL Server how to Pivot for multiple columns

Question

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

@saad you need to respond something for your posted question . Otherwise no one will help you in future — mohan111, Jun 28 '16 at 05:27

score 43 · Answer 1 · answered May 21 '18 at 06:12

43

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.

answered May 21 '18 at 06:12

Ankur Gupta

449
4
5

9

The key point here is that you create new category values by appending `1` or `2` to the end. Without doing this, the pivot query won't work properly. – Tim Biegeleisen Sep 28 '18 at 10:03
2

This one should be marked as the correct answer. – gio Jan 21 '21 at 14:21

score 24 · Answer 2 · answered Jun 28 '16 at 05:23

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

score 5 · Answer 3 · edited Jul 28 '19 at 10:29

5

Try below solution

  -- Applying pivoting on multiple columns
SELECT
*
FROM
(
  SELECT 
   Category,
   Sales, 
  FROM TblPivot
 ) AS P

 -- For Sales
 PIVOT
 (
   SUM(Sales) FOR Category IN ([Panel], [AC], [Ref])
 ) AS pv1

union all

 -- For Stock
 SELECT
*
FROM
(
  SELECT 
   Category,
   Stock, 
  FROM TblPivot
 ) AS P

 PIVOT
 (
   SUM(Stock) FOR Category IN ([Panel], [AC], [Ref])
 ) AS pv2

union all

 -- For Target
 SELECT
*
FROM
(
  SELECT 
   Category,
   Target, 
  FROM TblPivot
 ) AS P

 PIVOT
 (
   SUM(Target) FOR Category IN ([Panel], [AC], [Ref])
 ) AS pv3
 GO

edited Jul 28 '19 at 10:29

Khairul Alam

1,266
2
11
31

answered Jun 28 '16 at 05:24

Vignesh Kumar A

27,863
13
63
115

7

I got 'Invalid column name 'Category'. Can you please advice? – user482745 Apr 19 '18 at 10:17

score 5 · Answer 4 · answered Nov 09 '21 at 12:22

From Answer1 & Answer2

(this not OP's requirement)

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

score 4 · Answer 5 · edited Jan 18 '19 at 08:21

4

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

edited Jan 18 '19 at 08:21

ray

5,454
1
18
40

answered Jan 18 '19 at 07:53

suraj thakur

81
4

score 0 · Answer 6 · answered Jul 15 '21 at 08:19

You could first UNPIVOT the data to make it a flat table and then PIVOT to turn the categories into columns:

SELECT *
FROM SampleTable
UNPIVOT (Amount FOR Output IN (Sales, Stock, Target)) upvt
PIVOT (SUM(Amount) FOR Category IN (Panel, AC, Ref)) pvt
ORDER BY Branch, Output;

In SQL Server how to Pivot for multiple columns

6 Answers6

Linked