CODEWITHSUNDEEP
sqlsql-server
Jordan Davis
Jordan Davis

Reputation: 875

Add missing rows to SQL query

I have a data set in which I need each issue to have 3 rows. 1 for each status that is possible. Below is an example of what I currently have. 

Issue  Status  Time 
-------------------
1      SLM      30
1      SNB      43
1      EOB      22
2      SLM      12
2      EOB      87

I need something like this, where is an issue doesn't have a status then a row is added and 0 is set for the time.

 Issue   Status    Time 
 ----------------------
    1      SLM      30
    1      SNB      43
    1      EOB      22
    2      SLM      12
    2      EOB      87
    2      SNB      0

How can I do this?

Upvotes: 0

Views: 1696

Answers (2)

Sean
Sean

Reputation: 100

You can easily add another row by using this sql command.

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

for instance if your tables name is

Upvotes: -1

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

Cross join id's with statuses and left join the table on to that.

select i.issue,s.status,coalesce(t.time,0) as time
from (select distinct status from tbl) s --replace this with status table if you have one
cross join (select distinct issue from tbl) i
left join tbl t on t.issue=i.issue and t.status=s.status

Upvotes: 8

Related Questions