Pivot table representation in SQL server [Resolved]

Posted by Saisindura under Sql Server on 5/7/2015 | Points: 10 | Views : 882 | Status : [Member] | Replies : 1

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

[Resolved]

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	--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

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

Latest Posts