一张桌子上的现金和支票

Question

我需要从一张2栏的桌子上得到业务员订购的现金和支票金额的总和。

我的桌子是这样的：

Salesman A  Cash    21151
Salesman B  Cash    34510
Salesman C  Cash    21252
Salesman D  Cash    13356
Salesman A  Cheque  13944
Salesman B  Cheque  87368
Salesman C  Cheque  20182
Salesman D  Cheque  23862

SQL:

SELECT Ca.Cash,  Cr.Credit FROM 
    (SELECT Salesman, PayMode, SUM(Collection) AS Cash
        FROM Prospect_Detail WHERE(Purpose = 'Collections') AND (PayMode = 'Cash')
        GROUP BY Salesman, PayMode) AS Ca 
        CROSS JOIN
    (SELECT Salesman, PayMode, SUM(Collection) AS Credit
        FROM Prospect_Detail WHERE(Purpose = 'Collections') AND (PayMode = 'cheque')
        GROUP BY Salesman, PayMode) Cr

我要得到这样的结果：

Salesman       Cash      Cheque
Salesman A     21151     13944
Salesman B     34510     87368
Salesman C     21252     20182
Salesman D     13356     23862

Answer 1

你可以按以下方式转动你的桌子

With c as(
SELECT Salesman
     , Paymode
     , Sum(Collection) AS Cheque
FROM 
    Prospect_Detail
GROUP BY 
      Salesman
    , Paymode
)
Select Salesman
      ,[Cash]
      ,[Cheque] 
From C
Pivot(
    Sum(Cheque)
    For Paymode in ([Cash],[Cheque]) 
) as pvt;

我希望这对你有用。

Answer 2

您可以尝试以下方法。

create table #Temp (name varchar(50), collectionType varchar(10), amount int)
insert into #Temp values
('Salesman A', 'Cash', 21151),
('Salesman B', 'Cash', 34510),
('Salesman C', 'Cash', 21252),
('Salesman D', 'Cash', 13356),
('Salesman A', 'Cheque', 13944),
('Salesman B', 'Cheque', 87368),
('Salesman C', 'Cheque', 20182),
('Salesman D', 'Cheque', 23862)

SELECT name, Cash, cheque 
FROM  
(SELECT collectionType, name , amount 
    FROM #Temp) AS SourceTable  
PIVOT  
(  
SUM(amount)  
FOR collectionType IN ( Cash ,  Cheque)  
) AS PivotTable;

输出如下所示

name        Cash    cheque
--------------------------
Salesman A  21151   13944
Salesman B  34510   87368
Salesman C  21252   20182
Salesman D  13356   23862

您可以找到现场演示这里。

Answer 3

关于转向的建议在这方面是一个巨大的过于复杂的问题。别理他们。只需使用条件求和。一个简化的例子：

select Salesman, sum(case Paymode when 'Cash' then Collection else 0 end) as Cash, ...
from Prospect_Detail 
where Purpose = 'Collections'
group by Salesman 
order by Salesman;