拆分成多列的問題，有什么比較好的方法

[hycheng163]

拆分列的問題
create table #k (rid varchar(10),item varchar(50))

insert into #k select '1001','F721-WH-WH-10'
insert into #k select '1002','F765-WH-WH-04'
insert into #k select '1003','F772-IV-IV-10'
insert into #k select '1004','T643-IV-IV-10'
insert into #k select '1005','T687-WH-WH-10'
insert into #k select '1006','T643-WH-WH-04'
insert into #k select '1007','T744-IV-IV-20'
insert into #k select '1008','F717A-IV-IV-10'

結果：

1001 F721 WH WH 10
1002 F721 WH WH 10
1003 F765 WH WH 04
1004 F772 IV IV 10
1005 T643 IV IV 10
1006 T687 WH WH 10
1007 T643 WH WH 04
1008 T744 IV IV 20
1009 F717A IV IV 10

[CN_SQL]

楼主看下精华帖

[pt1314917]

--如果不超过4列的话：
create table #k (rid varchar(10),item varchar(50)) 
insert into #k select '1001','F721-WH-WH-10' 
insert into #k select '1002','F765-WH-WH-04' 
insert into #k select '1003','F772-IV-IV-10' 
insert into #k select '1004','T643-IV-IV-10' 
insert into #k select '1005','T687-WH-WH-10' 
insert into #k select '1006','T643-WH-WH-04' 
insert into #k select '1007','T744-IV-IV-20' 
insert into #k select '1008','F717A-IV-IV-10' 


select rid,parsename(item,4),parsename(item,3),parsename(item,2),parsename(item,1) from 
(select rid,replace(item,'-','.') item from #k)a