Unpivot table data

Question

 Sno    Water   Milk
  1      50     100
  2      22     120
  3      11     142

i have this table.Now i want result like

  Sno   Type   Qnty
   1    Water   83
   2     Milk   362

How can I please tell me.

Answer 1

SQL Server 2008 does not support this kind of statement.

You can achieve that in 2 ways:

using temporary table (variable type table) DECLARE @products TABLE(Sno INT, Water INT, Milk INT)

 INSERT INTO @products
 VALUES (1, 50, 100), (2, 22, 120), (3, 11, 142)


 SELECT ROW_NUMBER() OVER(ORDER BY SUM(Qnty)) AS RowNo, Product, SUM(Qnty) AS Qnty
 FROM (
  SELECT Product, Qnty
 FROM (
    SELECT *
    FROM @products
     ) AS pvt
 UNPIVOT (Qnty FOR Product IN ([Water],[Milk])) AS unpvt
 ) AS T
 GROUP BY Product</pre>

or

;WITH T AS
 (
 SELECT Sno, Water, Milk
  FROM (
  SELECT 1 AS Sno, 50 AS Water, 100 AS Milk
  UNION ALL
  SELECT 2, 22, 120
  UNION ALL
  SELECT 3, 11, 142
  ) t (Sno, Water, Milk))
   SELECT  Sno = ROW_NUMBER() OVER(ORDER BY SUM(Upvt.Qnty)),
    upvt.Type,
    Qnty = SUM(Upvt.Qnty)
    FROM    T
   UNPIVOT
    (   Qnty
        FOR Type IN ([Water], [Milk])
    ) upvt
   GROUP BY upvt.Type
   ORDER BY Qnty;</pre>

   Please,refer MSDN documentation.