Generate a dummy row-CodePudding

I need to repeat the data row on certain condition even if no data is present for it. Eg.

ID	Date	Name
1	7/1/22	A
1	8/1/22	A
2	7/1/22	B
2	8/1/22	B
3	7/1/22	C
4	7/1/22	D
4	8/1/22	D

I need a row for id 3 for date 8/1/22, there is no data in the database .

CodePudding user response：

To generate rows for an id that does not have a corresponding row for a given date in SQL, you can use a SELECT statement with a LEFT JOIN to a subquery that generates a list of all possible id and date combinations. The LEFT JOIN will return all rows from the left table (the main table in the SELECT statement), and any matching rows from the right table (the subquery). If a row does not exist in the right table for a given id and date, the NULL values will be returned for the right table's columns.

Here is an example of how you could do this in SQL:

SELECT t.id, t.date, t.name
FROM your_table t
LEFT JOIN (
    SELECT id, '8/1/22' as date
    FROM your_table
    GROUP BY id
) s
ON t.id = s.id AND t.date = s.date

This will return all rows from your_table, and if a row with id 3 and date '8/1/22' does not exist in the table, it will return a NULL value for the name column for that row.

CodePudding user response：

Here is an that will solve for any missing combination via an CROSS JOIN and NOT EXITS

Example or dbFiddle

--Insert Into YourTable 
Select A.ID
      ,Date= B.Date
      ,A.Name
 From YourTable A
 Cross Join (Select Distinct [Date] from YourTable) B 
 Where Not Exists ( Select 1 From YourTable where ID=A.ID and Name=A.Name and Date=B.Date)

Results

ID  Date    Name
3   8/1/22  C