add blank row between groups of rows to separate them?
Rich
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--
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
-- 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
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
What makes you so sure that tiered architecture is used in this
particular case?
Rich
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
> 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
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.
Andreas Kunst
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 ***