Pivot table representation in SQL server [Resolved]

Posted by Saisindura under Sql Server on 5/7/2015 | Points: 10 | Views : 407 | Status : [Member] | Replies : 1
Hi have the data like below

XQ3 XQ4 YQ1 YQ2 YQ3 YQ4 ZQ1 ZQ2 ZQ3 ZQ4
1 2 3 4 5 6 7 8 9 10 11 12
1 2 3 4 5 6 7 8 9 10 11 12
1 2 3 4 5 6 7 8 9 10 11 12
How can I represent like below

Q1 Q2 Q3 Q4
X Sum (XQ1) Sum (XQ2) Sum (XQ3) Sum (XQ4)
Y Sum (YQ1) Sum (YQ2) Sum (YQ3) Sum (YQ4)
Z Sum (ZQ1) Sum (ZQ2) Sum (ZQ3) Sum (ZQ4)

Thanks&Regards
srisai



Responses

Posted by: Bandi on: 5/12/2015 [Member] [MVP] Platinum | Points: 50

Up
0
Down

Resolved
--your Sample Data in consumable format

DECLARE @tab TABLE(XQ1 int, XQ2 int,XQ3 int,XQ4 int,YQ1 int,YQ2 int,YQ3 int,YQ4 int,ZQ1 int,ZQ2 int,ZQ3 int,ZQ4 int)
insert @tab
SELECT 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12 union all
SELECT 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12 union all
SELECT 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12
--How can I represent like below

--Q1 Q2 Q3 Q4
--X Sum (XQ1) Sum (XQ2) Sum (XQ3) Sum (XQ4)
--Y Sum (YQ1) Sum (YQ2) Sum (YQ3) Sum (YQ4)
--Z Sum (ZQ1) Sum (ZQ2) Sum (ZQ3) Sum (ZQ4)


--Query
SELECT GroupingSet, SUM(Q1) Q1,SUM(Q2) Q2,SUM(Q3) Q3,SUM(Q4) Q4 
FROM
(
SELECT LEFT( ColumnNames,1) GroupingSet, ColumnData, ColumnNames,
RIGHT( ColumnNames,2) RowSet
FROM @tab
UNPIVOT (ColumnData FOR ColumnNames IN (XQ1,XQ2,XQ3,XQ4,YQ1,YQ2,YQ3,YQ4,ZQ1,ZQ2,ZQ3,ZQ4)
) unpvt )TempUnPvt
PIVOT (SUM(ColumnData) FOR RowSet IN (Q1,Q2,Q3,Q4))Pvt
GROUP BY GroupingSet


--OUTPUT
GroupingSet	Q1	Q2	Q3	Q4
X 3 6 9 12
Y 15 18 21 24
Z 27 30 33 36


Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Saisindura, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response