Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Insert Blank row between result Expand / Collapse
Author
Message
Posted Tuesday, March 25, 2014 11:05 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, April 19, 2014 8:15 PM
Points: 42, Visits: 207

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
Post #1554596
Posted Tuesday, March 25, 2014 11:38 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 5:19 PM
Points: 3,374, Visits: 7,303
It can be done in SQL, but if you're using a reporting tool, you should do it in there.


Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1554610
Posted Tuesday, March 25, 2014 12:00 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 12:07 PM
Points: 15,562, Visits: 27,940
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1554623
Posted Tuesday, March 25, 2014 12:02 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, April 19, 2014 8:15 PM
Points: 42, Visits: 207
Thank you for replying query. Can you please share SQL and report soulation ?



Post #1554626
Posted Tuesday, March 25, 2014 12:55 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 5:19 PM
Points: 3,374, Visits: 7,303
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.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1554650
Posted Tuesday, March 25, 2014 4:58 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Today @ 10:11 PM
Points: 710, Visits: 4,544
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';

Post #1554720
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse