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

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

Answer 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

Answer 3

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

Answer 4

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

Answer 5

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

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

Answer 7

The answer by Ankur Gupta gives the technique to use. You can have multiple pivots, but they cannot share the same "column that contains the values that become column headers" as Microsoft's documentation puts it. You need to create additional columns by appending '1', '2' etc to the string, and then pivot using these. Here is a self-contained example. Create a temporary table with data:

drop table if exists #t
create table #t (n varchar(10) not null, x int not null, y int not null)
insert into #t values ('a', 1, -1), ('a', 2, -2), ('b', 10, -10), ('c', 100, -100)

Now to do a single pivot is straightforward:

select a, b, c
from #t
pivot (sum(x) for n in ("a", "b", "c")) p

That gives

a	b	c
null	null	100
null	10	null
2	null	null
1	null	null

(You'd probably want to sum the values again after pivoting. The sum inside the pivot subquery doesn't do much.)

So far we have fetched values of the x column. If we want to pivot y too we need a new set of column headings. The easiest way is to add a suffix to each value of n:

with augmented as (select *, n + '2' as n2 from #t)
select *
from augmented
pivot (sum(x) for n in ("a", "b", "c")) p1
pivot (sum(y) for n2 in ("a2", "b2", "c2")) p2

That gives the values of x in columns a, b, c and the values of y in a2, b2, c2:

a	b	c	a2	b2	c2
null	null	100	null	null	-100
null	10	null	null	-10	null
1	null	null	-1	null	null
2	null	null	-2	null	null