Insert blank lines in table to group data....
-
Hi I wish to group data and insert a blank line in a table to make it easier to see the grouped data. i.e.:
Co_Name, Co_Address
abc ltd , 123 street
fort ltd , 123 street
<blank row>
123 ltd , 345 Road
test ltd , 345 Road
89 plc , 345 Road
<blank row>
etc....
The group by is on Co_Address. Currently all the data is in one table, and I just need a blank row to separate the grouped by addresses. Any suggessions very welcome!!
Thanks in advance
-
declare a temporary table.
declare a cursor.
fetch data from cursor to local variables.
check the variable data and cursor data,
If both are equal, insert it into the temporary table
If not, insert blank row into the temporary table.
Continue still end of the cursor
Return the temporary table
-
well..this is a job for the client!
also..grouping by addresses is fraught with pitfalls..you should look for some other column/candidate to group on!!!
**ASCII stupid question, get a stupid ANSI !!!** -
We sure don't need a cursor for this and I'm not sure you could do it any faster or easier in the client
... heck... you don't even need a temp table...SELECT CASE WHEN GROUPING(Co_Name) = 1 THEN ' ' ELSE Co_Name END AS Co_Name,
CASE WHEN GROUPING(Co_Name) = 1 THEN ' ' ELSE Co_Address END AS Co_Address
FROM yourtable
GROUP BY Co_Address,Co_Name WITH ROLLUP
Co_Name Co_Address
-------------------- --------------------
abc ltd 123 street
fort ltd 123 street
123 ltd 345 Road
89 plc 345 Road
test ltd 345 Road
(8 row(s) affected)
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Your "thinking hat" is obviously one of high quality Jeff...
It doesn't however, tell you that grouping by addresses is ok, does it ?!?!
**ASCII stupid question, get a stupid ANSI !!!** -
Sure... why not? Perhaps they're trying to make a delivery route list to cut down on the number of stops that a truck has to make where street numbers and names would play an important role that address ID's would hide... maybe it's just some report that some beanie wanted. In either case, they're not storing it in the database that way so I don't think there's a problem with this... let's ask the requestor (although we might have asked that first, eh?)...
David, what are you going to do with this output?
By the way... good seeing you again, Sushila... it's always a pleasure.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply