Insert empty row between rows

[former_SQLA_member1694869]

I have a sql that returns a number of columns with values. I would like to insert a blank line after each new customer. How do I do that with a simple sql?

How it looks today:

customer----value1----value2---value3
-------------------------------------
volvo--------red-------23-------32---
volvo--------blue------29-------39---
volvo--------pink------43-------51---
toyota-------black-----26-------61---
toyota-------green-----19-------82---
toyota-------black-----11-------71---
saab---------black-----26-------61---
saab---------green-----19-------82---
saab---------black-----11-------71---


But I want it to be like this
customer----value1----value2---value3
-------------------------------------
volvo--------red-------23-------32---
volvo--------blue------29-------39---
volvo--------pink------43-------51---
------------empty row----------------
toyota-------black-----26-------61---
toyota-------green-----19-------82---
toyota-------black-----11-------71---
------------empty row----------------
saab---------black-----26-------61---
saab---------green-----19-------82---
saab---------black-----11-------71---

[VolkerBarth · Accepted solution]

While in general I share's Thomas's statement that this might be more a task for a report generator or the like, here's a different approach using OLAP functions and the builtin rowgenerator table.

Without knowing your table schema and actual query, I have tried something similar based on SA's demo database and its Products table, as that table has several rows with the same Name and has therefore a somewhat similar result set:

select Name, Description, Size from Products order by 1, 2;


returns the following:
Name,Description,Size
Baseball Cap    Cotton Cap    One size fits all
Baseball Cap    Wool cap    One size fits all
Shorts    Cotton Shorts    Medium
Sweatshirt    Hooded Sweatshirt    Large
Sweatshirt    Zipped Sweatshirt    Large
Tee Shirt    Crew Neck    One size fits all
Tee Shirt    Tank Top    Small
Tee Shirt    V-neck    Medium
Visor    Cloth Visor    One size fits all
Visor    Plastic Visor    One size fits all


First step: Number the resulting groups (i.e. same values for "Name") and according rows with the help of OLAP functions DENSE_RANK() and ROW_NUMBER();
select dense_rank() over (order by Name) as GroupNo,
   row_number() over (order by Name, Description) as RowNo, 
   Name, Description, Size
from Products
order by 1, 2;


returns 
1    1    Baseball Cap    Cotton Cap    One size fits all
1    2    Baseball Cap    Wool cap    One size fits all
2    3    Shorts    Cotton Shorts    Medium
3    4    Sweatshirt    Hooded Sweatshirt    Large
3    5    Sweatshirt    Zipped Sweatshirt    Large
4    6    Tee Shirt    Crew Neck    One size fits all
4    7    Tee Shirt    Tank Top    Small
4    8    Tee Shirt    V-neck    Medium
5    9    Visor    Cloth Visor    One size fits all
5   10    Visor    Plastic Visor    One size fits all


Second step: For each group, add an according "empty row" from rowgenerator using an UNION ALL query:
select dense_rank() over (order by Name) as GroupNo,
   row_number() over (order by Name, Description) as RowNo, 
   Name, Description, Size
from Products
union all
select row_num, null, null, null, null
from rowgenerator
where row_num <= (select count(distinct Name) from Products)
order by 1, 2


returns
GroupNo    RowNo    Name    Description    Size
1    (NULL)    (NULL)    (NULL)    (NULL)
1    1    Baseball Cap    Cotton Cap    One size fits all
1    2    Baseball Cap    Wool cap    One size fits all
2    (NULL)    (NULL)    (NULL)    (NULL)
2    3    Shorts    Cotton Shorts    Medium
3    (NULL)    (NULL)    (NULL)    (NULL)
3    4    Sweatshirt    Hooded Sweatshirt    Large
3    5    Sweatshirt    Zipped Sweatshirt    Large
4    (NULL)    (NULL)    (NULL)    (NULL)
4    6    Tee Shirt    Crew Neck    One size fits all
4    7    Tee Shirt    Tank Top    Small
4    8    Tee Shirt    V-neck    Medium
5    (NULL)    (NULL)    (NULL)    (NULL)
5    9    Visor    Cloth Visor    One size fits all
5    10   Visor    Plastic Visor    One size fits all


Third step: Finally build the query without the row numbers by using the previous query as a derived query. (These are basically two steps: Build one derived query to number the resulting rows, and then select from that derived query to omit the row numbers - but ordered by those row numbers):
select Name, Description, Size
from
   (select row_number() over (order by GroupNo, RowNo) as TotalRowNo,
         Name, Description, Size
    from
       (select dense_rank() over (order by Name) as GroupNo,
           row_number() over (order by Name, Description) as RowNo, 
           Name, Description, Size
        from Products
        union all
        select row_num, null, null, null, null
        from rowgenerator
        where row_num <= (select count(distinct Name) from Products)
        order by 1, 2) DT1
     order by 1) DT2
 order by TotalRowNo;


This finally returns
Name    Description    Size
(NULL)    (NULL)    (NULL)
Baseball Cap    Cotton Cap    One size fits all
Baseball Cap    Wool cap    One size fits all
(NULL)    (NULL)    (NULL)
Shorts    Cotton Shorts    Medium
(NULL)    (NULL)    (NULL)
Sweatshirt    Hooded Sweatshirt    Large
Sweatshirt    Zipped Sweatshirt    Large
(NULL)    (NULL)    (NULL)
Tee Shirt    Crew Neck    One size fits all
Tee Shirt    Tank Top    Small
Tee Shirt    V-neck    Medium
(NULL)    (NULL)    (NULL)
Visor    Cloth Visor    One size fits all
Visor    Plastic Visor    One size fits all

[Former Member]

I agree with the sentiment that this doesn't feel like a SQL problem, but hey, I wrote a calendar generator in SQL once (and a COBOL scanner in COBOL--but that's another story).

Here's my entry. It inserts an extra row for each distinct value (using Breck's approach) then sorts. I might be missing why the ROW_NUMBER is needed.

select case ord when 0 then Name end as name, Description, Size
from (
select 0 as ord, Name, Description, Size from Product 
union all
select 1 as ord, name, null, null from Product group by Name
) D
order by D.name, D.ord, D.description

[VolkerBarth]

That does insert an empty row after each group of values, so I guess to sort it the other way, you simply have to switch 0 and 1:

select case ord when 1 then Name end as name, Description, Size
from (
   select 1 as ord, Name, Description, Size from Products 
   union all
   select 0 as ord, name, null, null from Products group by Name
) D
order by D.name, D.ord, D.description


Otherwise, that's obviously a waaaay better solution than Breck's and mine - and much easier to understand as well... - thanks for sharing:)

[justin_willey]

but hey, I wrote a calendar generator in SQL once (and a COBOL scanner in COBOL--but that's another story) Ahh its the old problem isn't it - once you've got a hammer, every problem starts to look like a nail!

[Breck_Carter]

Well done, and it certainly satisfies the wish for "a query that could probably be simplified (because it just feels too complex :)..."

select case ord when 0 then Name end as name, Description, Size
from (
select 0 as ord, Name, Description, Size from Products 
union all
select 1 as ord, name, null, null from Products group by Name
) D
order by D.name, D.ord, D.description;

name,Description,Size
'Baseball Cap','Cotton Cap','One size fits all'
'Baseball Cap','Wool cap','One size fits all'
,,
'Shorts','Cotton Shorts','Medium'
,,
'Sweatshirt','Hooded Sweatshirt','Large'
'Sweatshirt','Zipped Sweatshirt','Large'
,,
'Tee Shirt','Crew Neck','One size fits all'
'Tee Shirt','Tank Top','Small'
'Tee Shirt','V-neck','Medium'
,,
'Visor','Cloth Visor','One size fits all'
'Visor','Plastic Visor','One size fits all'
,,

[VolkerBarth]

Well, isn't that a fine sample of the general division of labour:

No matter how overly-complex the queries are we figure out, Ivan (aka the query processing engine) will do the optimization:)

[Former Member]

Good point @Justin. Either that or when I pick up a heavy tool, I just want to start hitting anything nearby to see what happens.

[VolkerBarth]

Glad you wrote anything🙂

[Breck_Carter]

Assuming that the sample data is a complete and perfect representation of the problem, here is a query that could probably be simplified (because it just feels too complex :)...

CREATE TABLE t (
   inserted_order INTEGER NOT NULL DEFAULT AUTOINCREMENT PRIMARY KEY,
   customer       VARCHAR ( 10 ) NOT NULL,
   value1         VARCHAR ( 10 ) NOT NULL,
   value2         VARCHAR ( 10 ) NOT NULL,
   value3         VARCHAR ( 10 ) NOT NULL );

INSERT t VALUES ( DEFAULT, 'volvo', 'red', '23', '32' );
INSERT t VALUES ( DEFAULT, 'volvo', 'blue', '29', '39' );
INSERT t VALUES ( DEFAULT, 'volvo', 'pink', '43', '51' );
INSERT t VALUES ( DEFAULT, 'toyota', 'black', '26', '61' );
INSERT t VALUES ( DEFAULT, 'toyota', 'green', '19', '82' );
INSERT t VALUES ( DEFAULT, 'toyota', 'black', '11', '71' );
INSERT t VALUES ( DEFAULT, 'saab', 'black', '26', '61' );
INSERT t VALUES ( DEFAULT, 'saab', 'green', '19', '82' );
INSERT t VALUES ( DEFAULT, 'saab', 'black', '11', '71' );
COMMIT;

SELECT customer, value1, value2, value3
  FROM
       (
       SELECT ROW_NUMBER() OVER ( ORDER BY subset_customer DESC, subset_order, inserted_order ) AS row_number,
              IF subset_order = 1 THEN subset_customer ELSE '---' ENDIF AS customer,
              value1,
              value2,
              value3
         FROM
              (
              SELECT customer AS subset_customer, 1 AS subset_order, inserted_order, value1, value2, value3
                FROM t 
              UNION ALL
              SELECT customer AS subset_customer, 2, 0, '---', '---', '---' 
                FROM t
               GROUP BY customer 
              ) AS row_set
        ORDER BY subset_customer DESC, subset_order, inserted_order
       ) AS report
 ORDER BY row_number;

customer   value1     value2     value3     
---------- ---------- ---------- ---------- 
volvo      red        23         32         
volvo      blue       29         39         
volvo      pink       43         51         
---        ---        ---        ---        
toyota     black      26         61         
toyota     green      19         82         
toyota     black      11         71         
---        ---        ---        ---        
saab       black      26         61         
saab       green      19         82         
saab       black      11         71         
---        ---        ---        ---   

[thomas_duemesnil]

I thinks this is more of a front end problem that doesn't need to be fixed in a query.

Anyway you could try to figure something out with the help of grouping sets.

You could then group by customer and all other columns are set to NULL.

HTH