CODEWITHSUNDEEP
sql-serversql-server-2005stored-proceduresunpivot
santhosha
santhosha

Reputation: 377

Unpivot table data

 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.

Upvotes: 1

Views: 102

Answers (3)

Sambasiva
Sambasiva

Reputation: 1044

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.

Upvotes: 1

GarethD
GarethD

Reputation: 69789

The first step is to UNPIVOT your data:

WITH T AS
(   SELECT Sno, Water, Milk
    FROM (VALUES (1, 50, 100), (2, 22, 120), (3, 11, 142)) t (Sno, Water, Milk)
)
SELECT  upvt.Sno,
        upvt.Type,
        Upvt.Qnty
FROM    T
        UNPIVOT
        (   Qnty
            FOR Type IN ([Water], [Milk])
        ) AS upvt;

Which will give:

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

You can then apply normal aggregation to this result:

WITH T AS
(   SELECT Sno, Water, Milk
    FROM (VALUES (1, 50, 100), (2, 22, 120), (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])
        ) AS upvt
GROUP BY upvt.Type
ORDER BY Qnty;

Giving:

Sno Type    Qnty
1   Water   83
2   Milk    362

EDIT

Based on your comment that you are using SQL Server 2005, not 2008 as indicated below is a full working example that will work on 2005:

DECLARE @T TABLE (Sno INT, Water INT, Milk INT);
INSERT @T (Sno, Water, Milk) VALUES (1, 50, 100);
INSERT @T (Sno, Water, Milk) VALUES(2, 22, 120);
INSERT @T (Sno, Water, Milk) VALUES(3, 11, 142);

SELECT  Sno = ROW_NUMBER() OVER(ORDER BY SUM(Upvt.Qnty)),
        upvt.Type,
        Qnty = SUM(Upvt.Qnty)
FROM    @T AS T
        UNPIVOT
        (   Qnty
            FOR Type IN ([Water], [Milk])
        ) AS upvt
GROUP BY upvt.Type
ORDER BY Qnty;

It was the table valued constructor I was using to create the sample data that was causing the error in 2005, nothing to do with the actual query that unpivots then sums the data.

Upvotes: 1

Tristan
Tristan

Reputation: 1024

This gives the result you asked for but I get the idea you want something a little more advanced. If so please elaborate.

SELECT 'water', sum(water) FROM table_name
UNION ALL 
SELECT 'milk', sum(milk) FROM  table_name

Upvotes: 0

Related Questions