首页 > 解决方案 > 将多行压缩为一个总和行

问题描述

我正在尝试将 Quantity <=500 的所有行压缩为一个行条目,将它们加在一起

SELECT c.CompanyName
, d.NumNameCode
--, CAST((d.Quantity*10) as INT) as Quantityx10
, SUM(CASE
      WHEN Quantity <=500 THEN CAST((Quantity*10) as INT)
      ELSE ''
  END) as '<=500'
,  CASE
      WHEN Quantity > 500 THEN CAST((Quantity*10) as INT)
      ELSE ''
   END as '>500'
FROM [TFBO7].[dbo].[CustInv] ci
  inner join [TFBO7].[dbo].[Cust] c
  on c.CustomerID=ci.CustomerID
  inner join [TFBO7].[dbo].[CustIDet] d
  on ci.TransactionID=d.TransactionID      
WHERE ci.TransactionDate between '2010-07-01' and '2010-07-30' and d.TransactionTypeID='1' and NumNameCode LIKE '%gov%'
  group by NumNameCode, CompanyName, Quantity
  order by NumNameCode, '<=500'
GO

我的桌子看起来像这样

 CompanyName    NumNameCode       <=500     >500
 DESC           JET A*Gov Army      0        5030
 DESC           JET A*Gov Army      0        7140
 DESC           Jet A*Gov Army      0        7940
 DESC           Jet A*Gov Army    680          0
 DESC           Jet A*Gov Army    710          0
 DESC           Jet A*Gov Army    860          0

我需要将 <=500 的条目压缩为一个求和行

 CompanyName    NumNameCode       <=500     >500
 DESC           JET A*Gov Army      0        5030
 DESC           JET A*Gov Army      0        7140
 DESC           Jet A*Gov Army      0        7940
 DESC           Jet A*Gov Army      0        2250

标签: sqlsql-server

解决方案


正如您在代码中看到的那样,您已经group by打开了quantity,因此它将导致您的问题的所有唯一数量分组。您可以使用前面评论中建议的子查询,也可以Union像下面这样简单:

SELECT  CompanyName
      , NumNameCode
      , SUM(CASE WHEN quantity <= 500 THEN CAST(( quantity * 10 ) AS INT)
                 ELSE ''
            END) AS '<=500'
      , NULL AS '>500'
FROM    #temp2
WHERE   quantity < 500
GROUP BY NumNameCode , CompanyName
UNION ALL
SELECT  CompanyName
      , NumNameCode
      , NULL '<=500'
      , CAST(( quantity * 10 ) AS INT) '>500'
FROM    #temp2
WHERE   quantity >= 500
  GO

推荐阅读