Как отображать неназначенные строки при использовании Crosstab в Sqlserver

Я использовал кросс-таблицу в своем запросе для преобразования строки в столбец в соответствии с требованиями. Тем не менее я далек от желаемого результата. Здесь я дал некоторые тестовые данные

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

Любое предложение, пожалуйста?


sql repeat crosstab sql-server-2005
person Rumina    schedule 05.08.2011    source источник

Ответы (1)


arrow_upward
1
arrow_downward

Сначала вам нужно создать полный список продуктов и отделов, а затем оставить соединение с запросом, который вы придумали, например:


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

Вы не упомянули о существовании таблицы продуктов, если такая вещь существует, было бы лучше использовать ее при создании списка продуктов, чем выбор отдельного, который у меня есть здесь, против таблицы продаж.

person Jeremy Shimanek    schedule 05.08.2011
comment
У меня есть таблица продуктов... Но мне нужен только список продуктов, который использовался в таблице продаж... Поэтому я рад создать отдельный список продуктов из таблицы продаж... Вы сделали мой день... - person Rumina; 05.08.2011