--这样?
create table tb(CustID int,Contact varchar(50))
insert into tb select 33286,'382-7188/8641 (Tel)'
union all select 33286,'00-541-3823671 (FAX)'select id=identity(int,1,1),* into ## from tb
declare @sql varchar(8000)
set @sql='select CustID'
select @sql=@sql+',[Contact'+cast(id as varchar)+']=max(case cast(id as varchar) when '+cast(id as varchar)+' then Contact else '''' end)' from ## group by CustID,id
print @sql
exec(@sql+' from ## group by CustID')drop table tb,##
create table tb(CustID int,Contact varchar(50))
insert into tb select 33286,'382-7188/8641 (Tel)'
union all select 33286,'00-541-3823671 (FAX)'select id=identity(int,1,1),* into ## from tb
declare @sql varchar(8000)
set @sql='select CustID'
select @sql=@sql+',[Contact'+cast(id as varchar)+']=max(case cast(id as varchar) when '+cast(id as varchar)+' then Contact else '''' end)' from ## group by CustID,id
print @sql
exec(@sql+' from ## group by CustID')drop table tb,##
from
(select CustID,Contact as Contact1 from tab where Contact like '%Tel%')t1,
(select CustID,Contact as Contact2 from tab where Contact like '%FAX%')t2
where t1.CustID = t2.CustID
create table tb1(CustID int,Contact varchar(50))
insert into tb1 select 33286,'382-7188/8641 (Tel)'
union all select 33286,'00-541-3823671 (FAX)'--执行语句
select CustID, Contact1=max(case right(Contact,5) when '(Tel)' then Contact end),Contact1=max(case right(Contact,5) when '(FAX)' then Contact end)
from tb1
group by CustID
select CustID, Contact1=max(case when charindex('(Tel)',Contact)<>0 then Contact end),Contact2=max(case when charindex('(FAX)',Contact)<>0 then Contact end)
from tb1
group by CustID
select CustID, Contact1=max(case when patindex('%(Tel)%',Contact)<>0 then Contact end),Contact2=max(case when patindex('%(FAX)%',Contact)<>0 then Contact end)
from tb1
group by CustID