求SQL語句，字符串拆分問題

[Tosp2012]

現有表A結構如下：
Lot_Key F_Size
CA101 33.50x1.50x2.50x3.35
CA200 18.50x17.00x1.60
CG300 21.15x1.65x2.50x1.30x1.65
CQ255 31.50x1.75

需將A表的F_Size字段，以‘x’為分割符，拆分成5個字段F_Size01,F_Size02,F_Size03,F_Size04,F_Size05。
最終實現如下表
Lot_Key F_Size F_Size01 F_Size02 F_Size03 F_Size04 F_Size05
CA101 33.50x1.50x2.50x3.35 33.5 1.5 2.5 3.35 Null
CA200 18.50x17.00x1.60 18.5 17 1.6 Null Null
CG300 21.15x1.65x2.50x1.30x1.65 21.15 1.65 2.5 1.3 1.65
CQ255 31.50x1.75 31.5 1.75 Null Null Null

求教，謝！

[阳泉酒家小当家]

如果最多5列，可以这样试试：

--drop table a

create table A(Lot_Key varchar(15),	F_Size varchar(100))

insert into a
select 'CA101',	'33.50x1.50x2.50x3.35' union all
select 'CA200',	'18.50x17.00x1.60' union all
select 'CG300',	'21.15x1.65x2.50x1.30x1.65' union all
select 'CQ255',	'31.50x1.75'
go


select Lot_Key,F_Size,
       MAX(case when rownum = 1 then v else null end) F_Size01,
       MAX(case when rownum = 2 then v else null end) F_Size02,
       MAX(case when rownum = 3 then v else null end) F_Size03,
       MAX(case when rownum = 4 then v else null end) F_Size04,
       MAX(case when rownum = 5 then v else null end) F_Size05       	
from 
(
select Lot_Key,
       F_Size,
       SUBSTRING(t.F_Size, number ,CHARINDEX('x',t.F_Size+'x',number)-number) as v,
       ROW_NUMBER() over(partition by lot_key order by getdate()) rownum
from A t,master..spt_values s
where s.number >=1
and s.type = 'P'
and SUBSTRING('x'+t.F_Size,s.number,1) = 'x'
)t
group by Lot_Key,F_Size
order by Lot_Key
/*
Lot_Key	F_Size	F_Size01	F_Size02	F_Size03	F_Size04	F_Size05
CA101	33.50x1.50x2.50x3.35	33.50	1.50	2.50	3.35	NULL
CA200	18.50x17.00x1.60	18.50	17.00	1.60	NULL	NULL
CG300	21.15x1.65x2.50x1.30x1.65	21.15	1.65	2.50	1.30	1.65
CQ255	31.50x1.75	31.50	1.75	NULL	NULL	NULL
*/

[Tosp2012]

多謝施教，結貼