Link to home
Start Free TrialLog in
Avatar of Darius
DariusFlag for Lithuania

asked on

SQL - insert empty rows into output results

Hi guys,
I'm writing a SQL script to generate data with empty rows in between. How to get empty rows without results?
I'm stuck and I need help on query below.

DECLARE @memTableD1 TABLE ([logId]int, [dateadded]datetime, [message]varchar(MAX) );
DECLARE @memTableD2 TABLE ([logId]int, [dateadded]datetime, [message]varchar(MAX) );
	insert into @memTableD1
	select logId, dateAdded, [message] FROM logs order by logid desc;
	insert into @memTableD2 
	select logId, '', '' from @memTableD1;
	
	select * from  @memTableD1
	union
	select * from  @memTableD2
	order by logId desc;

Open in new window


User generated image
Thank you.
Avatar of ste5an
ste5an
Flag of Germany image

E.g.

DECLARE @memTableD1 TABLE
    (
       logId  INT ,
       dateadded  DATETIME ,
       [message]  VARCHAR(MAX)
    );
    
DECLARE @memTableD2 TABLE
    (
       logId  INT ,
       dateadded  DATETIME ,
       [message]  VARCHAR(MAX)
    );
    
INSERT  INTO @memTableD1
        SELECT  logId ,
                dateAdded ,
                [message] 
        FROM    logs
        ORDER BY logid DESC;
        
INSERT  INTO @memTableD2
        SELECT  logId ,
                NULL ,
                NULL
        FROM    @memTableD1;
 
SELECT  Iif(dateAdded = '19000101', NULL, logId ) AS logId,
        Iif(dateAdded = '19000101', NULL, dateAdded ) AS dateAdded ,
        Iif(dateAdded = '19000101', NULL, [message] ) AS [message] 
FROM    @memTableD1
UNION
SELECT  Iif(dateAdded = '19000101', NULL, logId ) AS logId,
        Iif(dateAdded = '19000101', NULL, dateAdded ) AS dateAdded ,
        Iif(dateAdded = '19000101', NULL, [message] ) AS [message] 
FROM    @memTableD2
ORDER BY logId DESC;

Open in new window


But you should do that in your reporting front-end.
Avatar of Darius

ASKER

Hi Mike,
I using sql output results to send as email. This way it more readable...
Hi Darius,
Please try below-

Note - IIF only works with SQL 2012+, Also note that you have to CASE only for DATE column. Please try-

DECLARE @memTableD1 TABLE ([logId]int, [dateadded]datetime, [message]varchar(MAX) );
DECLARE @memTableD2 TABLE ([logId]int, [dateadded]datetime, [message]varchar(MAX) );

INSERT INTO @memTableD1
SELECT logId, dateAdded, [message] FROM logs

INSERT INTO @memTableD2 
SELECT logId, NULL, NULL from @memTableD1;
	
SELECT logId, CASE WHEN dateAdded = '1900-01-01' OR dateAdded IS NULL THEN '' ELSE CAST(dateAdded AS VARCHAR(20)) END dateAdded , [message] FROM
(
	SELECT * FROM  @memTableD1
	UNION
	SELECT * FROM  @memTableD2
)k
ORDER BY logId DESC;

Open in new window


Sample Trial for you-

/*------------------------
SELECT Number , CASE WHEN Dates IS NULL OR DATES = '1900-01-01' THEN '' ELSE CAST(DATES AS VARCHAR(20)) END DATES FROM
(
	SELECT Number,Dates FROM PawanDates
	UNION
	SELECT Number , '' from PawanDates
)k
------------------------*/
Number      DATES
----------- --------------------
1           
1           2016-01-01
2           
2           2016-02-01
3           
3           2016-03-01
4           
4           2016-04-01
5           
5           2016-05-01
6           
6           2016-06-01
7           
7           2016-07-01
8           
8           2016-08-01
9           
9           2016-09-01
10          
10          2016-10-01
11          
11          2016-11-01
12          
12          2016-12-01

(24 row(s) affected)

Open in new window


Hope it helps!
Avatar of Darius

ASKER

Hi Pawan,
Results still unacceptable... I need to get logId rows empty as well! Please see details below

DECLARE @memTableD1 TABLE ([logId]int, [dateadded]datetime, [message]varchar(MAX) );
DECLARE @memTableD2 TABLE ([logId]int, [dateadded]datetime, [message]varchar(MAX) );

INSERT INTO @memTableD1
SELECT 
	logId, 
	dateAdded, 
	[message] 
	FROM logs;

INSERT INTO @memTableD2 
SELECT 
	logId, 
	NULL, 
	NULL 
	FROM @memTableD1;
	
SELECT 
	logId, 
	CASE WHEN dateAdded IS NULL OR dateAdded = '1900-01-01' THEN '' ELSE convert(varchar, dateAdded ,121) END 
	dateAdded,
	[message] 
FROM
(
	SELECT logId,dateAdded,message FROM logs
	UNION
	SELECT logId , '','' from logs
)k
ORDER BY logId DESC;

Open in new window



User generated image
Hi,
Please try..
SELECT 
	logId, 
	CASE WHEN dateAdded IS NULL OR dateAdded = '1900-01-01' THEN '' ELSE convert(varchar, dateAdded ,121) END 
	dateAdded,
	[message] 
FROM
(
	SELECT logId,dateAdded,message FROM logs
	UNION
	SELECT '' , '','' from logs
)k
ORDER BY logId DESC;

Open in new window

Avatar of Darius

ASKER

Getting full rows after last change...

User generated image
ASKER CERTIFIED SOLUTION
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Darius

ASKER

Thank you Pawan!!!

SELECT 	 
	CASE WHEN rnk % 1 = 0.0 THEN '' ELSE CAST(logId AS sql_variant) END logId,
	CASE WHEN rnk % 1 = 0.0 THEN '' ELSE convert(varchar, dateAdded ,121) END dateAdded,
	CASE WHEN rnk % 1 = 0.0 THEN '' ELSE CAST([message] AS varchar(max)) END [message] 
FROM
(
	SELECT logId,dateAdded,message  , ROW_NUMBER() OVER (ORDER BY logid) rnk FROM logs where level = 0
	UNION ALL
	SELECT logId,dateAdded,message  , ROW_NUMBER() OVER (ORDER BY logid) + 0.5 rnk FROM logs where level = 0
)k 
ORDER BY rnk desc

Open in new window

Welcome Darius. Glad to help.
A better question here would be 'Why do you wish to do this in T-SQL?"  Cosmetic formatting such as blank lines is better left to the reporting layer (SSRS, Crystal, Access, Tableau), and not the data layer (SQL Server).
Avatar of Darius

ASKER

Hi Jim, this is my next task to automate report!  This is was just a challenge for me...
  Thank you for advice.