SQL Server 2008 does not support this kind of statement.
[EDIT]
Above statement is not true. Please see Andrius's comment to my answer.
Thank you, Andrius ;)
You can achieve that in 2 ways:
1) 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
or
2) using
UNION ALL[
^] statement
;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;
Please,refer MSDN documentation.