Insert a blank line between groups

[123FakeSt]

I would like to get my query that outputs:
1
1
2
2
2
3
4

To look like:
1
1

2
2
2

3
4

I was thinking I could use:
SELECT ' ' INTO #temp

[MySelectStatementHere]
FROM [MyTable] CROSS JOIN #temp

But of course that doesn't work. My limitations are I can not use Cursors or Update statements, but I can use temp tables.

The early bird gets the worm, but the second mouse gets the cheese.

 

[SQLBill]

That's not really a job for SQL Server. SQL Server is a database and Query Analyzer is really just a tool to write and check scripts. This should be done in some other front-end application.

-SQLBill

Posting advice: FAQ481-4875

 

[123FakeSt]

Sure I could do it with Crystal, Access, Excel, Winword etc. The problem is the application I'm supporting will display the results of an SQL query directly. It is additional cost of ownership and development time to incorporate a third party application.

And I also like to be challenged with puzzles like this.

The early bird gets the worm, but the second mouse gets the cheese.

 

[Zathras]

It's a bit of a kludge, but you may be able to do something with this:
[tt]
create table #t (ANumber int, ADatum char(10))
insert into #t values (1,'A')
insert into #t values (1,'B')
insert into #t values (2,'A')
insert into #t values (2,'B')
insert into #t values (2,'C')
insert into #t values (3,'A')
insert into #t values (4,'A')

create table #work (seqno int, ANumber char(5))
insert into #work select ANumber*2,Convert(Char(5),ANumber) from #t
insert into #work select distinct ANumber*2+1,' ' from #t

select ANumber from #work order by seqno

drop table #work
drop table #t
[/tt]

 

[123FakeSt]

So close:

SELECT fk1, string1, 0 as makebottom
FROM myTable
WHERE string2 = 'something'

UNION ALL

SELECT DISTINCT fk1, ' ', 1 as makebottom
FROM myTable
WHERE string2 = 'something'

But how the heck do you SELECT INTO with a UNION query?

I just realized this is probably in the wrong forum. Sorry.


The early bird gets the worm, but the second mouse gets the cheese.

 

[vongrunt]

Some more circus art (2-columns query only):

select case when ADatum is null then null else ANumber end as ANumber,
	ADatum
from #t
group by ANumber, ADatum with rollup
-- having grouping(ANumber) = 0

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]