SQL - Creating Additional Lines of zero value data (Cross Join type?)

Question

I'm trying to figure out how to get a query to work. I kind of want to do a cross join, but not really a cross join type query.. nor is it a full join (I don't think) So I'm throwing it to the community for input.

I have table 1:

Acct  AcctDesc   CostType   Value1  Value2
12    School     Tax        12.42   3.20
12    School     Supplies   34.22   9.88
12    School     Payroll    122.00  8.88
12    School     Milk       8.88    7.77
13    Work       Tax        28.88   9.70
13    Work       Supplies   15.15   8.80
13    Work       Cookies    5.90    7.00
13    Work       Payroll    79.00   8.88
13    Work       Misc       4.33    3.33
13    Work       Vehicle    8.33    0.33

With this table, I have multiple CostTypes. I want to take all the unique costtypes and join them into data to create a view that will result (in this case for school) and will throw zeros in for the costs. (The use is to return zero columns on a report later)

Acct  AcctDesc   CostType   Value1  Value2
12    School     Tax        12.42   3.20
12    School     Supplies   34.22   9.88
12    School     Payroll    122.00  8.88
12    School     Milk       8.88    7.77
12    School     Cookies    0.00    0.00
12    School     Misc       0.00    0.00
12    School     Vehicle    0.00    0.00

I assumed I could do something like

Select Acct, AcctDesc, CostType, Value1, Value2 
from Table1
Cross Join (Select Distinct CostType from Table1) t2

But I quickly realized that it's just not going to work like that. I've also tried natural joins and that's not going to work either.

I'm thinking I may need to do a CTE with all unique values from the table, and then do a left join with the original query, which I think is the least optimal, so I wanted to throw it to you guys.

Thanks for your input.

Answer 1

This should work (although there might be better ways to do it):

SELECT 
    subq.Acct, 
    subq.AcctDesc, 
    subq.CostType, 
    Value1 = ISNULL(Value1,0), 
    Value2 = ISNULL(Value2,0) 
FROM (
    SELECT
       t1.CostType, 
       t2.Acct, 
       t2.AcctDesc 
    FROM Table1 t1 
    CROSS JOIN Table1 t2
    GROUP BY t1.CostType, t2.Acct, t2.AcctDesc
    ) subq
LEFT JOIN Table1 t ON subq.CostType = t.CostType 
                  AND subq.Acct = t.Acct
                  AND subq.AcctDesc = t.AcctDesc
--WHERE t.AcctDesc = 'School'
ORDER BY subq.Acct, subq.AcctDesc

Sample output:

Acct    AcctDesc         CostType         Value1             Value2
------- ---------------- ---------------- ------------------ ------------------
12      School           Cookies          0                  0
12      School           Milk             8,88               7,77
12      School           Misc             0                  0
12      School           Payroll          122                8,88
12      School           Supplies         34,22              9,88
12      School           Tax              12,42              3,2
12      School           Vehicle          0                  0
13      Work             Cookies          5,9                7
13      Work             Milk             0                  0
13      Work             Misc             4,33               3,33
13      Work             Payroll          79                 8,88
13      Work             Supplies         15,15              8,8
13      Work             Tax              28,88              9,7
13      Work             Vehicle          8,33               0,33