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