CODEWITHSUNDEEP
sqlsql-server
Nightmaresux
Nightmaresux

Reputation: 538

Fill the remaining categories SQL

I have a table Customer : 

UserId  Name    Amount
1       AA        1
1       BB        1
1       CC        1
1       DD        1
2       AA        1
2       BB        1
2       CC        1
3       AA        1
3       CC        1

And i need select where i will see for every UserID every name (AA,BB,CC,DD) - if he doesnt have a record with that name in the previous table i want too fill the amount with 0. So for given example the result should be:

UserId  Name    Amount
1        AA       1
1        BB       1
1        CC       1
1        DD       1
2        AA       1
2        BB       1
2        CC       1
2        DD       0
3        AA       1
3        BB       0
3        CC       1
3        DD       0

I dont think i can do that with "case when..." so maybe with some join on table with only all names in it..? Thank you for any help

Upvotes: 0

Views: 47

Answers (2)

ps_prakash02
ps_prakash02

Reputation: 583

Can you try this

SELECT B.UserId, A.Name, ISNULL(C.Amount,0) Amount
FROM 
(SELECT DISTINCT Name FROM TEMP) A
CROSS JOIN 
(SELECT DISTINCT USERID FROM TEMP) B 
LEFT JOIN TEMP C ON B.USERID = C.USERID AND A.NAME = C.NAME
ORDER BY B.USERID, A.NAME

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270573

Think about this in two steps. First get all the rows, then fill in the values. The first part uses a cross join. The second can be done with a left join (or correlated subquery):

select u.userid, n.name, coalesce(.amount, 0) as amount
from (select distinct userid from customer) u cross join
     (select distinct name from customer) n left join
     customer c
     on c.userid = u.userid and c.name = n.name;

Upvotes: 3

Related Questions