how can i get such a result set?

For example I hv got a table 'Customer' like this:

Customer_name Customer_phone
Iverson 1234567
Kobe 2345678
Tmac 3456789
Wade 5678901
James 6789012
Bosh 7890123
Iverson 8901234
Wade 9012345
James 2987654
Iverson 3567890

.

.

.

.

And now I want to get a display like this:

Customer_name Customer_phone1 Customer_phone2 Customer_phone3
Iverson 1234567 8901234
3567890
Wade 5678901 9012345
James 6789012 2987654
Kobe 2345678
Tmac 3456789
Bosh 7890123

Please note that I don't know how many columns (Customer_phone) would appear, i just want to display all. how can i do it? thanks!

How about below one, (need SQL Server 2005).

-

Code Snippet

create table
CREATE TABLE [dbo].[T1546140](
[Customer_name] [varchar](50) NOT NULL,
[Customer_phone] [varchar](50) NOT NULL
) ON [PRIMARY];

-- insert test data
insert into T1546140 values ('Iverson','1234567');
insert into T1546140 values ('Kobe','2345678');
insert into T1546140 values ('Tmac','3456789');
insert into T1546140 values ('Wade','5678901');
insert into T1546140 values ('James','6789012');
insert into T1546140 values ('Bosh','7890123');
insert into T1546140 values ('Iverson','8901234');
insert into T1546140 values ('Wade','9012345');
insert into T1546140 values ('James','2987654');
insert into T1546140 values ('Iverson','3567890');

-- declare values
declare @.phoneCount int;
declare @.query nvarchar(max);
declare @.n int;

-- get max phone count;
select @.phoneCount = max(c)
from
(
select Customer_name, count(*) as c
from T1546140 (nolock)
group by customer_name
) cn

-- build a query for
set @.query =
N'
select
Customer_name,
';

set @.n = 1
while @.n <= @.phoneCount
begin
set @.query = @.query
+ N'[' + convert(varchar, @.n) + ']'
+ N' as Customer_phone' + convert(varchar, @.n);
if @.n < @.phoneCount
set @.query = @.query + ',';
set @.n = @.n + 1;
end

set @.query = @.query
+
N'
from
(
select
Customer_name,
Customer_phone,
ROW_NUMBER() OVER (PARTITION BY Customer_name ORDER BY Customer_phone) as [phone_number]
from T1546140 (nolock)
group by
Customer_name,
Customer_phone
) as c
PIVOT
(
max(Customer_phone)
for [phone_number]
in (
';

set @.n = 1
while @.n <= @.phoneCount
begin
set @.query = @.query
+ N'[' + convert(varchar, @.n) + ']';
if @.n < @.phoneCount
set @.query = @.query + ',';
set @.n = @.n + 1;
end

set @.query = @.query +
N'
)
) as p
order by
Customer_name
';

-- dump a query for debug purpose
print @.query;

-- execute query
exec sp_executesql @.query;

-- end;

This program create and execute below query.

Code Snippet

select
Customer_name,
[1] as Customer_phone1,
[2] as Customer_phone2,
[3] as Customer_phone3
from
(
select
Customer_name,
Customer_phone,
ROW_NUMBER() OVER (PARTITION BY Customer_name ORDER BY Customer_phone) as [phone_number]
from T1546140 (nolock)
group by
Customer_name,
Customer_phone
) as c
PIVOT
(
max(Customer_phone)
for [phone_number]
in ([1], [2], [3])
) as p
order by
Customer_name

|||

sorry this may not help because as i said before, i don't know how many phone columns would appear, so if one customer has 6000 phone no., then does that mean i hv to

select
Customer_name,
[1] as Customer_phone1,
[2] as Customer_phone2,
[3] as Customer_phone3,

.

.

.

.

.

[6000] as Customer_phone 6000

that ain't work i think, but thanks a lot buddy