CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Fadi
Fadi

Reputation: 191

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

Upvotes: 4

Views: 7842

Answers (5)

banazs
banazs

Reputation: 116

A general solution for any number of people (SQLFiddle - http://sqlfiddle.com/#!3/00677/25):

WITH 

maxRow AS (
  SELECT TOP 1 COUNT(*) maxRow FROM tbl_person GROUP BY family_id ORDER BY 1 DESC 
),

rn AS (
  SELECT 1 as rowNum
  UNION ALL
  SELECT rowNum + 1
  FROM rn 
  WHERE rowNum < (SELECT * FROM maxRow)
),

rnFi AS (
SELECT 
  * 
FROM 
  rn,
  (SELECT DISTINCT family_id FROM tbl_person) fi
)

SELECT
  rnFi.rowNum,
  rnFi.family_id,
  t.name
FROM
  rnFi
LEFT JOIN
  (SELECT   
    ROW_NUMBER() OVER(PARTITION BY family_id ORDER BY family_id) AS rowNum,
    family_id,
    name
  FROM
    tbl_person) t ON rnFi.family_id = t.family_id AND rnFi.rowNum = t.rowNum
ORDER BY
  2, 1
;

Upvotes: 0

Mike
Mike

Reputation: 2005

Add blank rows to last row of each group using recursive CTE:

with Q as(
 SELECT full_name as name, age, family_id,
        ROW_NUMBER() OVER(PARTITION BY family_id ORDER BY family_id) AS rowNum,
        count(1) over(partition by family_id) cnt
   FROM tbl_person
 union all
  select NULL,NULL,family_id, Q.rowNum+1, Q.rowNum+1
    from Q
   where Q.cnt=Q.rowNum and Q.rowNum < 4
)
select * from Q
 order by family_id, rowNum

Upvotes: 2

Edward Comeau
Edward Comeau

Reputation: 4084

I would use a cte Tally table to build a skeleton based on your unique family_id values, and cte Results table which can be left joined to the skeleton based on tally number = row number.

Declare @family_row_count int = 14;

-- Tally Table CTE script (SQL 2005+ only)
-- You can use this to create many different numbers of rows... for example:
-- You could use a 3 way cross join (t3 x, t3 y, t3 z) instead of just 2 way to generate a different number of rows.
-- The # of rows this would generate for each is noted in the X3 comment column below.
-- For most common usage, I find t3 or t4 to be enough, so that is what is coded here.
-- If you use t3 in ‘Tally’, you can delete t4 and t5.


; WITH
    -- Tally table Gen            Tally Rows:     X2                X3
t1 AS (SELECT 1 N UNION ALL SELECT 1 N),    -- 4            ,    8
t2 AS (SELECT 1 N FROM t1 x, t1 y),            -- 16            ,    64
t3 AS (SELECT 1 N FROM t2 x, t2 y),            -- 256            ,    4096
t4 AS (SELECT 1 N FROM t3 x, t3 y),            -- 65536        ,    16,777,216
t5 AS (SELECT 1 N FROM t4 x, t4 y),            -- 4,294,967,296,    A lot
Tally AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) N
          FROM t3 x, t3 y) -- Change the t3's to one of the other numbers above for more/less rows 

, cte_Skeleton
as (
    select
         tally.N as [row_number]
         , family.family_id
    from Tally tally
    cross join ( select family_id from tbl_person group by family_id ) family
    where tally.N <= @family_row_count
)

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

select
      skeleton.[row_number] as [rowNum]
    , person.name
    , person.age
    , skeleton.family_id

from cte_Skeleton skeleton

left join cte_person person
    on person.family_id = skeleton.family_id
    and person.rownum = skeleton.[row_number]

NB, because you've defined the skeleton first and are left joining to it, even if a family_id contains more than 14 people, only the first 14 will have skeleton rows to match in the join.

Upvotes: 1

Me.Name
Me.Name

Reputation: 12544

If you use a tally table (because it's only 4 numbers it can be inline) and you cross apply them to all family_ids, you get records of numbers 1 to 4 for each family_id. Then left joining on both nr and family_id, the result would contain 4 rows for each id's where only the data of the existing rows are filled. (To prevent double referencing, you can use a cte on the main table to get both id's and do the left join, but you could also do the group by directly on the main table)

with p as
(
    SELECT   *,ROW_NUMBER() OVER(PARTITION BY family_id ORDER BY family_id) AS rowNum  
    FROM tbl_person
)
select nr as row_num, full_name as name, f.family_id
from (values(1),(2),(3),(4)) as nrs(nr) --inline tally table
cross apply (select family_id from p group by family_id) f --group by family id to get all existing id's
left join  p on p.rowNum = nr and f.family_id = p.family_id

edit Since you need more rows, it's easier (for creating and changing afterwards) to use some sort of tally table. One of my personal favourites is using an existing sys table:

with p as
(
    SELECT   *,ROW_NUMBER() OVER(PARTITION BY family_id ORDER BY family_id) AS rowNum  
    FROM tbl_person
)
select nr as row_num, full_name as name, f.family_id
from (select top 14 ROW_NUMBER() over (order by (select 1)) nr from sys.all_columns) nrs --use sys table for tallying
cross apply (select family_id from p group by family_id) f
left join  p on p.rowNum = nr and f.family_id = p.family_id
order by family_id,nr

Upvotes: 2

Bacon Bits
Bacon Bits

Reputation: 32145

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;

Upvotes: 4

Related Questions