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.

Store the types in a separate table and reference them in your data table? — G_V, Oct 22 '14 at 12:53

jpw · Accepted Answer · 2014-10-22T18:49:50.020

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

I tried to apply your logic to a dataset with more costtypes, AcctDescs, etc.. and it gets exponentially larger... with a lot more duplication. I don't think this is going to work. What I really need is something that follows the scenario "here is a defined list of items every account should have. The source table only shows values that are not null." The answer should be 'This is how we display all accounts and items with null item lines being shown as 'zero'. — Dregalia, Oct 22 '14 at 16:33
I kept fiddling with it.. you're right on there, but I did a few tweaks and it seems to be working as expected. — Dregalia, Oct 22 '14 at 17:36
@Dregalia Ah i see, I thought the Acct and AcctDesc were related so I didn't include the last join. Updated now. — jpw, Oct 23 '14 at 00:56
Thanks for helping me work thru it. I knew I had to be close. I really appreciate it. — Dregalia, Oct 23 '14 at 21:44

score 0 · Answer 2 · answered Oct 22 '14 at 17:44

This here seems to be working

SELECT 
    subq.Acct, 
    subq.AcctDesc, 
    subq.CostType, 
    Value1 = ISNULL(Value1,0), 
    Value2 = ISNULL(Value2,0) 
FROM (
    SELECT
       t2.CostType, 
       t1.Acct, 
       t1.AcctDesc 
    FROM Table1 t1 
    CROSS JOIN (select distinct CostType from Table1) t2
    GROUP BY t2.CostType, t1.Acct, t1.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

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

2 Answers2