fancy select needed.Thanks for your help!

Hi,
I have a table like the following:
ID | Name
--
1 | Name 1
1 | Name 2
2 | Name 3
2 | Name 4
2 | Name 5
I now want to do a select which results in the following:
ID | Name
--
1 | Name 1, Name 2
2 | Name 3, Name 4, Name 5
Does anybody know how this could be done? Thank you a lot in advance!
DanielBetter to do it in your client app / reporting tool / programming language.
Do not stress the server.
AMB
"Daniel Walzenbach" wrote:

> Hi,
> I have a table like the following:
>
> ID | Name
> --
> 1 | Name 1
> 1 | Name 2
> 2 | Name 3
> 2 | Name 4
> 2 | Name 5
>
> I now want to do a select which results in the following:
> ID | Name
> --
> 1 | Name 1, Name 2
> 2 | Name 3, Name 4, Name 5
> Does anybody know how this could be done? Thank you a lot in advance!
> Daniel

fancy select needed.Thanks for your help!

Here you go:
create table tt (
colA int,
colB varchar(10)
)
insert into tt values(1,'NAME 1')
insert into tt values(1,'NAME 2')
insert into tt values(2,'NAME 3')
insert into tt values(2,'NAME 4')
insert into tt values(2,'NAME 5')
select * from tt
colA colB
-- --
1 NAME 1
1 NAME 2
2 NAME 3
2 NAME 4
2 NAME 5
select cola, col1, col2, col3 = ( select min(colb) from
tt where tt2.cola = tt.cola and tt.colb > tt2.col2 )
from
(
select cola, col1, col2 = ( select min(colb) from tt
where tt1.cola = tt.cola and tt.colb > tt1.col1 )
from
(
select cola, min(colb) col1
from tt
group by cola
) tt1
) tt2
cola col1 col2 col3
-- -- -- --
1 NAME 1 NAME 2 NULL
2 NAME 3 NAME 4 NAME 5

>--Original Message--
>Hi,
>I have a table like the following:
>
>ID | Name
>--
>1 | Name 1
>1 | Name 2
>2 | Name 3
>2 | Name 4
>2 | Name 5
>
>I now want to do a select which results in the following:
>ID | Name
>--
>1 | Name 1, Name 2
>2 | Name 3, Name 4, Name 5
>Does anybody know how this could be done? Thank you a
lot in advance!
> Daniel
>.
>Thank you!
"Lito Dominguez" <anonymous@.discussions.microsoft.com> schrieb im
Newsbeitrag news:197d01c504b3$217eeab0$a601280a@.phx.gbl...
> Here you go:
> create table tt (
> colA int,
> colB varchar(10)
> )
> insert into tt values(1,'NAME 1')
> insert into tt values(1,'NAME 2')
> insert into tt values(2,'NAME 3')
> insert into tt values(2,'NAME 4')
> insert into tt values(2,'NAME 5')
> select * from tt
> colA colB
> -- --
> 1 NAME 1
> 1 NAME 2
> 2 NAME 3
> 2 NAME 4
> 2 NAME 5
> select cola, col1, col2, col3 = ( select min(colb) from
> tt where tt2.cola = tt.cola and tt.colb > tt2.col2 )
> from
> (
> select cola, col1, col2 = ( select min(colb) from tt
> where tt1.cola = tt.cola and tt.colb > tt1.col1 )
> from
> (
> select cola, min(colb) col1
> from tt
> group by cola
> ) tt1
> ) tt2
> cola col1 col2 col3
> -- -- -- --
> 1 NAME 1 NAME 2 NULL
> 2 NAME 3 NAME 4 NAME 5
>
> lot in advance!