help

id name
1 Noor
1 Ali
2 Hamsan
2 Guy
I want the data like this.
1 Noor Ali
2 Hamsan Guy
Can any body tell me
thanks
NOOR
Hi Noor,
Look into the below solution, this will fail if you have more than 2 records
for 1 id. If you have more than 2 id's then write a Cursor to obtain the
result.
I feel that this may not be possible only with a select statement.
set nocount on
CREATE TABLE #T (i int,a varchar(50))
INSERT INTO #T VALUES (1,'Hari')
INSERT INTO #T VALUES (1,'Prasad')
INSERT INTO #T VALUES (2,'SQL')
INSERT INTO #T VALUES (2,'DBA')
CREATE TABLE #T1 (i int,a varchar(1000))
--select * from #T t1 where a in (select top 1 t2.a from #T t2 where t1.i =
t2.i)
insert into #T1 select * from #T t1 where a in (select top 1 t2.a from
#T t2 where t1.i = t2.i)
update #T1 set a = t4.a + t3.a from #T1 t4, #T t3 where t4.i = t3.i
select * from #T1
drop table #T1
drop table #T
Thanks
Hari
MCDBA
"Noorali Issani" <naissani@.softhome.net> wrote in message
news:OWjwVmwIEHA.1608@.TK2MSFTNGP11.phx.gbl...
> id name
> 1 Noor
> 1 Ali
> 2 Hamsan
> 2 Guy
>
> I want the data like this.
>
> 1 Noor Ali
> 2 Hamsan Guy
> Can any body tell me
> thanks
> NOOR
>
|||Thanks Hari, can you tell me how I use your solution in Mysql b/c in mysql
we can't use inner query.
Thanks
Noor
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:%23IXv%23K6IEHA.1412@.TK2MSFTNGP12.phx.gbl...
> Hi Noor,
> Look into the below solution, this will fail if you have more than 2
records
> for 1 id. If you have more than 2 id's then write a Cursor to obtain the
> result.
> I feel that this may not be possible only with a select statement.
> set nocount on
> CREATE TABLE #T (i int,a varchar(50))
> INSERT INTO #T VALUES (1,'Hari')
> INSERT INTO #T VALUES (1,'Prasad')
> INSERT INTO #T VALUES (2,'SQL')
> INSERT INTO #T VALUES (2,'DBA')
> CREATE TABLE #T1 (i int,a varchar(1000))
> --select * from #T t1 where a in (select top 1 t2.a from #T t2 where t1.i
=
> t2.i)
> insert into #T1 select * from #T t1 where a in (select top 1 t2.a from
> #T t2 where t1.i = t2.i)
> update #T1 set a = t4.a + t3.a from #T1 t4, #T t3 where t4.i = t3.i
> select * from #T1
> drop table #T1
> drop table #T
> Thanks
> Hari
> MCDBA
>
> "Noorali Issani" <naissani@.softhome.net> wrote in message
> news:OWjwVmwIEHA.1608@.TK2MSFTNGP11.phx.gbl...
>