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
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.
-- 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
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
Following should work,
You gotta change the name of columns for next Pivot Statement.
Like
You are ready to go now....
You can use aggregate of pv3 to sum and group by the column you need.
Try below solution
Sample Table :
IN SQL SERVER Script :