May 31, 2011 at 10:30 am
Is there a way to insert a Blank row in the "output" of a query.
I have a simple table with Location, Date, and amount columns.
My query output needs to have a blank row inserted after a change in Location. It makes it easier to read. I would prefer to do this directly in the query - rather then run it through a report writer.
Thanks
May 31, 2011 at 11:31 am
Yes, there are ways to do that in a query. They are all horrible and will make your query really, really slow, and really, really hard to maintain.
You're much better off doing that kind of thing in the presentation layer. That's what N-tier applications are for. It might take a minute to put it into a report, but it won't slow the whole query down, et al. And it'll be easier to do.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 31, 2011 at 1:09 pm
Here's an option that may work, it's a little weird, but give it a try:
The basic premise is take your original query that selects your three columns and add a fourth column that is also the location (aliased as BlankRow). Next UNION that to a query with nulls for the first three columns and the same fourth column being location (BlankRow). That Query now becomes a dynamic table by SELECTing the first three columns from it, thus eliminating the BlankRow column that was used to create the blank rows to begin with. I ran a simple test that appeared to work, let me know if it works for you...
SELECT
Location,
Date,
Amount
FROM
(
SELECT
Location,
Date,
Amount,
Location AS BlankRow
FROM Test
UNION
SELECT
null,
null,
null,
Location AS BlankRow
FROM Test
GROUP BY Location
) AS Tbl
ORDER BY BlankRow ASC
May 31, 2011 at 7:09 pm
What I've found is that if a "pretty" graphical output isn't required, that it takes a whole lot longer to transfer the data to something like SSRS or Crystal, time for it to render the pretty picture, and finally return it to the user especially if the user isn't at the same site as the database server or the reporting server because a graphical report contains a whole lot more bytes than a simple ASCII printout.
I realize that most folks won't return 100,000 rows to the screen but check the following test out... it's not such a burden as most would think especially if you can avoid going to a 3rd party app or you just need to "print to file". 🙂
--===== Conditionally drop temp table(s) to make reruns easier in SSMS
IF OBJECT_ID('TempDB..#Test','U') IS NOT NULL DROP TABLE #Test;
--===== Let's build some test data so we can see if doing such a
-- thing is slow or not. 100,000 rows should be enough.
SELECT TOP (100000)
Location = ABS(CHECKSUM(NEWID()))%100+1,
Date = DATEADD(dd,ABS(CHECKSUM(NEWID()))%365,'2011'),
Amount = ABS(CHECKSUM(NEWID()))%100000/100.00
INTO #Test
FROM sys.All_Columns ac1
CROSS JOIN sys.All_Columns ac2
;
--===== Let the tests begin...
SET STATISTICS TIME ON;
--===== See how long it takes to return
-- a plain result set.
SELECT Location, Date, Amount
FROM #Test
ORDER BY Location, Date
;
--===== sdvoranchik's blank method
SELECT
Location,
Date,
Amount
FROM
(
SELECT
Location,
Date,
Amount,
Location AS BlankRow
FROM #Test
UNION
SELECT
null,
null,
null,
Location AS BlankRow
FROM #Test
GROUP BY Location
) AS Tbl
ORDER BY BlankRow,Date
SET STATISTICS TIME OFF;
--Jeff Moden
Change is inevitable... Change for the better is not.
May 31, 2011 at 7:15 pm
Almost forgot... here're the stats output from my desktop...
(100000 row(s) affected)
(100000 row(s) affected)
SQL Server Execution Times:
CPU time = 625 ms, elapsed time = 3332 ms.
(100099 row(s) affected)
SQL Server Execution Times:
CPU time = 672 ms, elapsed time = 3415 ms.
Not bad at all.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply