CODEWITHSUNDEEP
sqlsql-server
Kaja
Kaja

Reputation: 3057

Repeat rows in a table based on a column

I have two tables. The first Table TBL1 has only one column, which includes date:

[date]
2017-10-03                 
2017-10-04
2017-10-05
2017-10-06
....

The second table TBL2:

Date            Code           Index        Domain
2017-10-03       1               542         xxx.com
2017-10-03       680             652         xxx.com
2017-10-06       1               235         xxx.com
2017-10-06       680             125         xxx.com

I would like to have such a structure:

Date            Code           Index        Domain
2017-10-03       1               542         xxx.com
2017-10-03       680             652         xxx.com
2017-10-04       1               NULL        xxx.com
2017-10-04       680             NULL        xxx.com
2017-10-05       1               NULL        xxx.com
2017-10-05       680             NULL        xxx.com
2017-10-06       1               235         xxx.com
2017-10-06       680             125         xxx.com

How can I generate something like this in SQL?

Upvotes: 0

Views: 75

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271241

Use a cross join to generate the rows and a left join to bring in the columns:

select t1.date, cd.code, t2.index, cd.domain
from tbl1 t1 cross join
     (select distinct code, domain from tbl2) cd left join
     tbl2 t2
     on t2.date = t1.date and t2.code = cd.code and t2.domain = cd.domain;

Upvotes: 2

Related Questions