Fadi Fadi - 1 month ago 7
SQL Question

Insert number of blank rows in SQL

Using a SQL query, I need to include blank rows in the result to make every group of

(family_id)
equal to 4 rows

SELECT
ROW_NUMBER() OVER(PARTITION BY family_id ORDER BY family_id) AS rowNum,
full_name as name, family_id
FROM
tbl_person


Like this:

original result

and the result will be

enter image description here

Answer

Just define a second query that builds the empty rows that you need and union them together.

SELECT ROW_NUMBER() OVER(PARTITION BY family_id ORDER BY family_id) AS rowNum,
    full_name as name,
    family_id
FROM tbl_person

UNION ALL

SELECT n.N AS rowNum,
    NULL AS name,
    p.family_id
FROM (SELECT family_id, COUNT(*) family_count FROM tbl_person group by family_id) p
INNER JOIN (
    SELECT 1 AS N
    UNION ALL SELECT 2
    UNION ALL SELECT 3
    UNION ALL SELECT 4
    ) n
    ON n.n > p.family_count
ORDER BY family_id,
    rowNum,
    name;
Comments