How to display unassigned rows while using Crosstab in Sqlserver

Question

I have used crosstab in my query to convert row to column according to requirement. Still I am a away from the desired result. Here I have given some test data

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

And here is the output

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

When I use crosstab it gives me the following result

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

But I need to display the All departments against each Product with default zero value,if not exists.

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

Any suggestion please?

score 1 · Accepted Answer · answered Aug 05 '11 at 02:59

You need to generate a complete list of products and departments first, then left join to the query you came up with, like this:


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

You didn't mention the existence of a product table, if such a thing exists it would be better to use that in the product list generation than the select distinct I have here against the sales table.

I do have a product table...But i only need the product list that has been used in Sales table...So I am happy to generate distinct product list from Sales Table...You made my day... — Rumina, Aug 05 '11 at 03:38

How to display unassigned rows while using Crosstab in Sqlserver

1 Answers1