query to use

Hi
i have 2 tables. Table 2 can contain some values for each record in
table1. may vary for the no:of records in table2 for each record in
table1
table1
=====
id name
1 Arun
2 Hari
Table2
=====
id table1.id some_field
1 1 x
2 1 y
3 1 z
4 2 d
I want to get a display like the following
id name 1 2 3
1 Arun x y z
or
id name 1
1 Hari d
What query I have to use
?Hi
--SQL Server 2000
create table #table1 (id int,name varchar(50))
insert into #table1 values(1,'Arun')
insert into #table1 values(2,'Hari')
create table #table2 (id int,anotherid int, some_field varchar(50))
insert into #table2 values(1,1,'x')
insert into #table2 values(2,1,'y')
insert into #table2 values(3,1,'z')
insert into #table2 values(4,2,'d')
select * from #table1
select * from #table2
select name,max(case when rn=1 then some_field end) as '1',
max(case when rn=2 then some_field end) as '2',
max(case when rn=3 then some_field end) as '3'
from
(
select t2.anotherid,t2.some_field,count(*)rn from #table2,#table2 t2
where t2.anotherid=#table2.anotherid and t2.id<=#table2.id
group by t2.anotherid,t2.some_field
) as d join #table1 on d.anotherid=#table1.id
group by name
--SQL Server 2005
select * from
(
select t1.id ,name,anotherid,some_field,ROW_NUMBER() OVER(
PARTITION BY anotherid
ORDER BY some_field) AS pos
from #table1 AS t1
join #table2 AS t2
ON t1.id = t2.anotherid
) as der
pivot
(
max(some_field)
FOR pos IN([1], [2], [3], [4])
) AS PVT
<arunonw3@.gmail.com> wrote in message
news:1176193651.677044.91870@.l77g2000hsb.googlegroups.com...
> Hi
> i have 2 tables. Table 2 can contain some values for each record in
> table1. may vary for the no:of records in table2 for each record in
> table1
> table1
> =====
> id name
> 1 Arun
> 2 Hari
>
> Table2
> =====
> id table1.id some_field
> 1 1 x
> 2 1 y
> 3 1 z
> 4 2 d
> I want to get a display like the following
>
> id name 1 2 3
> 1 Arun x y z
> or
> id name 1
> 1 Hari d
> What query I have to use
> ?
>|||Thank you very much for sending me such a useful answer|||If you dont mind can you please explain the last 2 queriessql