'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

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

sqlsql-serverpivot


Solution 1:[1]

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.

Solution 2:[2]

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

Solution 3:[3]

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

Solution 4:[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

Solution 5:[5]

From Answer1 & Answer2

(this not OP's requirement)

enter image description here

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

Solution 6:[6]

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;

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Ankur Gupta
Solution 2 mohan111
Solution 3 Khairul Alam
Solution 4 ray
Solution 5 Palanikumar
Solution 6 Wolfgang Kais

Related Questions