Insert Blank row between result

Question

Insert Blank row between result

kshatriya24

SSC Eights!

Points: 887
More actions
March 25, 2014 at 11:05 am

#305809

Here is test data
Create Table #TestData
(Location varchar(100),
casenumber varchar(100),
complainedate datetime)
Insert Into #TestData Values ('dfw','123','2014-02-21 12:40:33.000')
Insert Into #TestData Values ('dfw','452','2014-03-17 10:29:26.000')
Insert Into #TestData Values ('dfd','478','2013-02-21 12:40:33.000')
Insert Into #TestData Values ('dfd','789','2012-02-21 12:40:33.000')
Insert Into #TestData Values ('dfs','125','2013-02-21 12:40:33.000')
Insert Into #TestData Values ('dfs','145','2015-02-21 12:40:33.000')
Select *
From #TestData
drop Table #TestData
Result - Out put of SQL
Locationcasenumbercomplainedate
dfw1232014-02-21 12:40:33.000
dfw4522014-03-17 10:29:26.000
dfd4782013-02-21 12:40:33.000
dfd7892012-02-21 12:40:33.000
dfs1252013-02-21 12:40:33.000
dfs1452015-02-21 12:40:33.000
How do i aggregate the location and insert blank row between records. End user would like to see blank row inserted when location change on the report for better visibility
Locationcasenumbercomplainedate
dfw1232014-02-21 12:40:33.000
dfw4522014-03-17 10:29:26.000
dfd4782013-02-21 12:40:33.000
dfd7892012-02-21 12:40:33.000
dfs1252013-02-21 12:40:33.000
dfs1452015-02-21 12:40:33.000
Any help apperciated.
Thank you

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

It can be done in SQL, but if you're using a reporting tool, you should do it in there.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 2

That's what a reporting tool is for. SQL Server and T-SQL are a horrible place to do that kind of formatting.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

kshatriya24 SSC Eights! Points: 887 More actions · Answer 3

Thank you for replying query. Can you please share SQL and report soulation ?

Luis Cazares SSC Guru Points: 183706 More actions · Answer 4

Why don't you try to get the solution?

I can't give a great advice on the reporting tool, but you should look for grouping options and totals (group footers), for T-SQL you can find a reference in here: http://technet.microsoft.com/en-us/library/bb522495(v=sql.105).aspx

Give it a try and post any specific questions you have.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

pietlinden SSC Guru Points: 63503 More actions · Answer 5

I like the empty Totals row option (hint hint)... see if you can figure that part out. You won't learn anything if you don't try things for yourself.

BTW, it makes it much easier for people to help if they can recreate your situation or dataset... something like this:

SELECT 'dfw' AS Location,123 AS CaseNumber,'2014-02-21 12:40:33.000' AS ComplaintDate UNION ALL

SELECT 'dfw',452,'2014-03-17 10:29:26.000' UNION ALL

SELECT 'dfd',478,'2013-02-21 12:40:33.000' UNION ALL

SELECT 'dfd',789,'2012-02-21 12:40:33.000' UNION ALL

SELECT 'dfs',125,'2013-02-21 12:40:33.000' UNION ALL

SELECT 'dfs',145,'2015-02-21 12:40:33.000';