取消透视表数据

debugcn 发表于 Dev

Santhosha

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

我有这张桌子。现在我想要这样的结果

  Sno   Type   Qnty
   1    Water   83
   2     Milk   362

我怎么能告诉我。

桑巴斯瓦

SQL Server 2008不支持这种语句。

您可以通过两种方式实现这一目标：

使用临时表（变量类型表）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>

或者

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

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。