Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Insert Blank row between result


Insert Blank row between result

Author
Message
kshatriya24
kshatriya24
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 Visits: 236
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

Location casenumber complainedate
dfw 123 2014-02-21 12:40:33.000
dfw 452 2014-03-17 10:29:26.000
dfd 478 2013-02-21 12:40:33.000
dfd 789 2012-02-21 12:40:33.000
dfs 125 2013-02-21 12:40:33.000
dfs 145 2015-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

Location casenumber complainedate
dfw 123 2014-02-21 12:40:33.000
dfw 452 2014-03-17 10:29:26.000


dfd 478 2013-02-21 12:40:33.000
dfd 789 2012-02-21 12:40:33.000


dfs 125 2013-02-21 12:40:33.000
dfs 145 2015-02-21 12:40:33.000

Any help apperciated.
Thank you
Luis Cazares
Luis Cazares
SSCrazy Eights
SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)

Group: General Forum Members
Points: 8506 Visits: 18114
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
Grant Fritchey
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17576 Visits: 32259
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

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
kshatriya24
kshatriya24
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 Visits: 236
Thank you for replying query. Can you please share SQL and report soulation ?
Luis Cazares
Luis Cazares
SSCrazy Eights
SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)

Group: General Forum Members
Points: 8506 Visits: 18114
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
pietlinden
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2178 Visits: 12502
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';


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search