如何使用sql server解开表? [英] how to unpivot table using sql server?
本文介绍了如何使用sql server解开表?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
WITH T AS
( SELECT Sno,Water,Milk
FROM ( VALUES ( 1 , 50 , 100 ),( 2 , 22 , 120 ),( 3 , 11 , 142 ))t(Sno,水,牛奶)
)
SELECT Sno = ROW_NUMBER() OVER ( ORDER BY SUM(Upvt.Qnty)),
upvt。< span class =code-keyword> Type ,
Qnty = SUM(Upvt.Qnty)
FROM T
UNPIVOT
(数量
FOR 类型 IN ([水], [牛奶])
)upvt
GROUP BY upvt。类型
ORDER BY Qnty;
它的工作正常http://sqlfiddle.com/#!3/d41d8/36356 [ ^ ]
但在我的sql中没有激发..它显示关键字'VALUES'附近的语法错误。
解决方案
SQL Server 2008不支持这种声明。
以上陈述不属实。请看Andrius对我的回答的评论。
谢谢你,Andrius;)
你可以实现这个目标有两种方式:
1)使用临时表(变量类型表)
DECLARE @ products TABLE (Sno INT ,Water INT ,Milk INT )
INSERT INTO @ products
VALUES ( 1 , 50 , 100 ),( 2 , 22 , 120 ),( 3 , 11 , 142 温泉n>)
SELECT ROW_NUMBER() OVER (< span class =code-keyword> ORDER BY SUM(Qnty)) AS RowNo ,产品,SUM(Qnty) AS Qnty
FROM (
SELECT 产品,Qnty
FROM (
SELECT *
FROM @ products
) AS pvt
UNPIVOT(Qnty FOR 产品 IN ( [Water],[Milk])) AS unpvt
) AS T
GROUP BY 产品; WITH T AS
(
SELECT Sno,Water,Milk
FROM (
SELECT 1 AS Sno, 50 AS 水, 100 < span class =code-keyword> AS 牛奶
UNION 所有
SELECT 2 , 22 , 120
UNION ALL
SELECT 3 , 11 , 142
)t(Sno,水,牛奶))
SELECT Sno = ROW_NUMBER() OVER ( ORDER BY SUM(Upvt.Qnty)),
upvt。 Type ,
Qnty = SUM(Upvt.Qnty)
FROM T
UNPIVOT
(Qnty
FOR 类型 IN ([水],[牛奶])
)upvt
GROUP BY upvt。类型
订购 BY Qnty;
请参阅MSDN文档。
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])
) upvt
GROUP BY upvt.Type
ORDER BY Qnty;
It works fine in http://sqlfiddle.com/#!3/d41d8/36356[^]
But in my sql is not excuting..it shows an "Incorrect syntax near the keyword 'VALUES'.
解决方案
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.
这篇关于如何使用sql server解开表?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文