Darius
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.
Thank you.
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;
Thank you.
ASKER
Hi Mike,
I using sql output results to send as email. This way it more readable...
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-
Sample Trial for you-
Hope it helps!
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;
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)
Hope it helps!
ASKER
Hi Pawan,
Results still unacceptable... I need to get logId rows empty as well! Please see details below
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;
Hi,
Please try..
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;
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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).
ASKER
Hi Jim, this is my next task to automate report! This is was just a challenge for me...
Thank you for advice.
Thank you for advice.
Open in new window
But you should do that in your reporting front-end.