Generate missing rows in TSQL

January 12, 2023

I am looking for an elegant solution to generate some missing integers in SQL.

An example would be:

rn	name	value
1	dim1	100
2	dim1	150
3	dim1	200
4	dim1	150
5	dim1	100
6	dim1	50
7	dim1	0
1	dim2	600
2	dim2	700
3	dim2	500

from there, I would like to UNION the following:

rn	name	value
4	dim2	0
5	dim2	0
6	dim2	0
7	dim2	0

Question being, how best to genrate the second table?
Assume maximum value for rn is the same for dim1, dim2, and any other dims.

>Solution :

You can join all your combinations of rns and names with your original table, then use COALESCE to fill missing values with 0. This will avoid you the UNION and doesn’t need to know in advance what’s the max amount of values for each name.

WITH combinations AS (
    SELECT *
    FROM       (SELECT DISTINCT rn   FROM tab) rns
    CROSS JOIN (SELECT DISTINCT name FROM tab) names 
)
SELECT c.*, COALESCE(tab.value_, 0) AS value_
FROM      combinations c
LEFT JOIN tab
       ON c.rn = tab.rn AND c.name = tab.name
ORDER BY name, rn

Check the demo here.