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

An example would be:

from there, I would like to UNION the following:

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

CodePudding user response：

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

It should work with more complex data.

CodePudding user response：

CREATE TABLE t1 (rn int, name varchar(10), value int)

INSERT INTO t1 VALUES
(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)

You could join the table with itself to get all dims/names and join again with itself to get missing rn values for each name.

SELECT DISTINCT base.rn, dims.name, 0 
FROM t1 AS base
INNER JOIN t1 AS dims ON base.name<>dims.name
LEFT JOIN t1 AS missing ON base.rn=missing.rn AND missing.name=dims.name
WHERE missing.rn IS NULL
ORDER BY 2,1

This would also assure that every dim has every nr: As soon as a nr is specified in any dim, this query will return the nr for every other dim in the table.