Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

add blank row between groups of rows to separate them?

4,072 views
Skip to first unread message

Rich

unread,
Jun 11, 2007, 1:17:01 PM6/11/07
to
Greetings,

I want to add a blank row between groups of rows to separate them.

select * from tbl1 where fname in ('Bill', 'tom', 'sam')

returns

Bill, 1, 2, 3
bill 4, 5, 6
tom, 1, 2, 3
tom, 4, 5, 6
sam, 1, 2, 3
sam, 4, 5, 6

I want to return this:

Bill, 1, 2, 3
bill 4, 5, 6

tom, 1, 2, 3
tom, 4, 5, 6

sam, 1, 2, 3
sam, 4, 5, 6

How can I perform this type of operation with tsql?

Thanks,
Rich

--CELKO--

unread,
Jun 11, 2007, 1:24:31 PM6/11/07
to
>> How can I perform this type of operation with tsql? <<

Why are you formatting data in the back end? The basic principle of a
tiered architecture is that display is done in the front end and never
in the back end. This a more basic programming principle than just
SQL and RDBMS.

Alex Kuznetsov

unread,
Jun 11, 2007, 1:42:55 PM6/11/07
to

-- for text output
SELECT n, CASE WHEN ROW_NUMBER() OVER(ORDER BY N) % 3 = 0 AND
ROW_NUMBER() OVER(ORDER BY N) > 1 THEN CHAR(13) ELSE ' ' END
FROM (
SELECT 1 N
UNION ALL
SELECT 2
UNION ALL
SELECT 3
UNION ALL
SELECT 41
UNION ALL
SELECT 51
UNION ALL
SELECT 61
UNION ALL
SELECT 71
UNION ALL
SELECT 72
) t

-- for grid output:
SELECT CASE WHEN t2.n=1 THEN t1.n END
FROM (SELECT N, ROW_NUMBER() OVER(ORDER BY N) rn FROM(
SELECT 1 N
UNION ALL
SELECT 2
UNION ALL
SELECT 3
UNION ALL
SELECT 41
UNION ALL
SELECT 51
UNION ALL
SELECT 61
UNION ALL
SELECT 71
UNION ALL
SELECT 72
) t
) t1
JOIN
(
SELECT 1 N
UNION ALL
SELECT 2
) t2
ON t2.n = 1
OR (t1.rn % 3 = 0 AND t1.rn > 1 )

Rich

unread,
Jun 11, 2007, 1:41:01 PM6/11/07
to
Is there a way to add blank rows to a datapull when viewing the returned rows
in Query Analyzer so that

select * from tbl1 where fname in ('Bill', 'tom', 'sam')

looks like this?

Bill, 1, 2, 3
bill 4, 5, 6

tom, 1, 2, 3
tom, 4, 5, 6

sam, 1, 2, 3
sam, 4, 5, 6

besides using a cursor or a loop?

Alex Kuznetsov

unread,
Jun 11, 2007, 1:44:37 PM6/11/07
to

What makes you so sure that tiered architecture is used in this
particular case?

Rich

unread,
Jun 11, 2007, 2:19:02 PM6/11/07
to
I don't understand one possible solution that was posted, so the solution I
will use is to generate a list of keys (fname in my example) and loop through
that list and insert each group of rows into a #temp table and insert a blank
row after each insert.

I was hoping there was a simpler way to do it without a loop.

David Portas

unread,
Jun 11, 2007, 6:33:57 PM6/11/07
to
On 11 Jun, 19:19, Rich <R...@discussions.microsoft.com> wrote:
> I don't understand one possible solution that was posted, so the solution I
> will use is to generate a list of keys (fname in my example) and loop through
> that list and insert each group of rows into a #temp table and insert a blank
> row after each insert.
>

I think you have a LOT to learn about SQL. A table has no ordering so
what you have said here makes no sense at all. You don't need a loop
to do what you have asked, however I would strongly encourage you to
utilise whatever facility to have in your presentation layer rather
than attempt this in SQL.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Steve Dassin

unread,
Jun 11, 2007, 10:22:35 PM6/11/07
to
"Rich" <Ri...@discussions.microsoft.com> wrote in message
news:508AD441-2993-45FD...@microsoft.com...

> I don't understand one possible solution that was posted, so the solution
I
> will use is to generate a list of keys (fname in my example) and loop
through
> that list and insert each group of rows into a #temp table and insert a
blank
> row after each insert.

Good for you. In a place where principles are often confused with
the poor murky judgement of the many, you show some plain old
common sense. You've decided not to do that which you don't
understand. In case everyone has forgot this is called taking
responsibility. And it has less to do with code than good judgement.

www.beyondsql.blogspot.com


Andreas Kunst

unread,
Jun 12, 2007, 1:17:42 AM6/12/07
to
I don't want to argue about how much sense it makes to format the query
results within the query analyzer, but there is a way to do what you
want.

Try this:

select
case
when Sort = '2' then ''
else name
end,
a,
b,
c
from
(
select
'1' Sort,
name,
a,
b,
c
from
tbl1
union
select
distinct
'2' Sort,
name,
NULL a,
NULL b,
NULL c
from
tbl1
) temp
order by
name,
Sort


I needed to assume the column names 'name', 'a', 'b', 'c' for the table
tbl1.

I hope that this is one possible solution that you understand. If not
don't hesitate to ask about details.

Andreas

*** Sent via Developersdex http://www.developersdex.com ***

0 new messages