如何在 Sqlserver 中使用交叉表时显示未分配的行

Question

我在查询中使用交叉表根据要求将行转换为列。但我距离想要的结果还有一段距离。这里我给出了一些测试数据

Declare  @tblDepartment Table
(
    DepartmentID int,
    DepartmentName nvarchar(10) 

)
Insert into @tblDepartment
Select 30,'AA'
union 
Select 31,'BB'
union 
Select 32,'CC'
union 
Select 33,'DD'
union 
Select 34,'EE'

SELECT * FROM @tblDepartment
/*************************************************/
Declare  @tblCurrency Table
(
    CurrencyID int,
    CurrencyName nvarchar(10)   

)

Insert into @tblCurrency

Select 1,'AUD'
union 
Select 2,'USD'
union 
Select 3,'Euro'
union 
Select 4,'GBP'

SELECT * FROM @tblCurrency


/*************************************************/
Declare  @tblSale Table
(
    ProductID int,
    DepartmentID int,   
    CurrencyID int,
    Value money

)

Insert into @tblSale

Select 1,   30, 2,  160.00 UNION
Select 1,   30, 3,  91927.00 UNION
Select 1,   32, 3,  914426.00 UNION
Select 1,   34, 4,  121.00 UNION
Select 2,   33, 4,  121.00 UNION
Select 2,   32, 4,  121.00 UNION
Select 2,   33, 2,  100.00 UNION
Select 2,   33, 3,  2000.00 UNION
Select 2,   33, 4,  121.00 UNION
Select 2,   32, 2,  52.00 UNION
Select 2,   32, 3,  5450.00 UNION
Select 2,   32, 4,  121.00 UNION
Select 2,   34, 1,  250.00 UNION
Select 2,   34, 2,  240.00 UNION
Select 2,   34, 3,  4540.00 UNION
Select 2,   34, 4,  8972.00

SELECT * FROM @tblSale

，这是输出

DepartmentID DepartmentName
    30  AA
    31  BB
    32  CC
    33  DD
    34  EE

CurrencyId CurrencyName
    1   AUD
    2   USD
    3   Euro
    4   GBP

ProductID DepartmentID CurrencyID Value
    1   30  2   160.00
    1   30  3   91927.00
    1   32  3   914426.00
    1   34  4   121.00
    2   33  4   121.00
    2   32  4   121.00
    2   33  2   100.00
    2   33  3   2000.00
    2   33  4   121.00
    2   32  2   52.00
    2   32  3   5450.00
    2   32  4   121.00
    2   34  1   250.00
    2   34  2   240.00
    2   34  3   4540.00
    2   34  4   8972.00

当我使用交叉表时，它给出了以下结果

Select ProductID, DepartmentID,
Sum(CASE CurrencyID When 1 then value else 0 END) as AUD,
Sum(CASE CurrencyID When 2 then value else 0 End) as USD,
Sum(CASE CurrencyID When 3 then value else 0 END) as EURO,
Sum(CASE CurrencyID When 4 then value else 0 End) as GBP
from 
(
    SELECT      T.ProductID, T.DepartmentID, T.CurrenCyID, T.Value
    FROM         @tblSale AS T 

) S
Group By ProductID, DepartmentID
Order By ProductID, DepartmentID


ProductID   DepartID    AUD         USD         Euro            GBP
1           30          0.00        160.00      91927.00        0.00
1           32          0.00        0.00        914426.00       0.00
1           34          0.00        0.00        0.00            121.00
2           32          0.00        52.00       5450.00         121.00
2           33          0.00        100.00      2000.00         121.00
2           34          250.00      240.00      4540.00         8972.00

但我需要显示每个产品的所有部门，默认为零值（如果不存在）。

ProductID   DepartID    AUD         USD         Euro            GBP
1           30          0.00        160.00      91927.00        0.00
1           31          0.00        0.00        0.00            0.00
1           32          0.00        0.00        914426.00       0.00
1           33          0.00        0.00        0.00            0.00
1           34          0.00        0.00        0.00            121.00
2           30          0.00        0.00        0.00            0.00
2           31          0.00        0.00        0.00            0.00
2           32          0.00        52.00       5450.00         121.00
2           33          0.00        100.00      2000.00         121.00
2           34          250.00      240.00      4540.00         8972.00

请问有什么建议吗？

Answer 1

您需要首先生成产品和部门的完整列表，然后将左连接添加到您提出的查询，如下所示：

select
    ProductID, 
    DepartmentID,
    isnull(AUD, 0) as AUD,
    isnull(USD, 0) as USD,
    isnull(EURO, 0) as EURO,
    isnull(GBP, 0) as GBP
from
(
    select DepartmentID from @tblDepartment
    cross join
    select distinct ProductID from @tblSale
) a
left join
    (
        Select ProductID, DepartmentID,
        Sum(CASE CurrencyID When 1 then value else 0 END) as AUD,
        Sum(CASE CurrencyID When 2 then value else 0 End) as USD,
        Sum(CASE CurrencyID When 3 then value else 0 END) as EURO,
        Sum(CASE CurrencyID When 4 then value else 0 End) as GBP
        from 
            @tblSale
        Group By 
            ProductID, DepartmentID
    ) b
on 
    a.DepartmentID = b.DepartmentID and a.ProductID = b.ProductID
Order By 
    a.ProductID, a.DepartmentID

您没有提到产品表的存在，如果存在这样的东西，那么在产品列表生成中使用它会比我在这里针对销售表选择的不同值更好。