any brilliant SQL masters out there? merging columns

[SoftwareEng]

guys... wtf, I can't figure this out

*** MS SQL Server 2000 ***

I have a view that returns data similar to :

John 1
John 2
John 3
Anna 4
Anna 5
Mike 6



And I need it to collapse columns into this:

John
1
2
3
Anna
4
5
Mike
6

(flatten output into one column, but ordered by the letter)
I tried Union and Cross-join to self, but nothing. Any ideas?

 

[KLin]

what sql edition? MySQL? MSSQL? Postgre SQL? Oracle? DB2? DETAILS PLEASE!

 

[SoftwareEng]

MS SQL Server 2000. Please help

 

[nickbits]

UNION should work.

You might have to CONVERT if both columns are not varchar

 

[SoftwareEng]

Originally posted by: nickbits
UNION should work.

You might have to CONVERT if both columns are not varchar

praying may also work, but could you please be more specific?

 

[BoberFett]

Well you're looking at a kind of grouping that SQL itself isn't really built for. SQL Reporting Services would handle that easily.

Assuming you're trying to keep the number data grouped with the person, you'll need to create yourself a sort column something like this.

select Info from (
select distinct Name as Info, Name as sort from #data
union
select cast(Num as varchar(10)) as Info, Name+cast(Num as varchar(10)) as sort from #data
) q
order by sort

 

[SoftwareEng]

BoberFett - thanks, I'm reading and understanding this

If it ends up being too difficult, I'll write a stored procedure.
I'm closing this topic for now.
Thanks guys.