Question

sql2000 表中一列内容拆分转多行语句

如图

这个是一行记录 ，考虑多条记录时类似
create table tb(ItemID int, InputUser varchar(10), InputItems varchar(220))
--insert into tb values(123 , 'Test' , '2347,0967,2567')
go

SELECT TOP 8000 id = IDENTITY(int, 1, 1) INTO # FROM syscolumns a, syscolumns b

SELECT A.ItemID , InputItems = SUBSTRING(A.[InputItems], B.id, CHARINDEX(',', A.[InputItems] + ',', B.id) - B.id) into fw
FROM tb A, # B
WHERE SUBSTRING(',' + A.[InputItems], B.id, 1) = ','
order by ItemID

select * from fw
where len(InputItems)>1
order by ItemID

找了一段代码，解决。谢谢internet

Answer 1

--搭建环境

create table #(a int,b varchar(60))

insert into # select 1,'123,456,xxx,789' union all select 2,'321,213,sss,985'

select * from #

go

------------------------------测试---------------------------------------------

--update组成查询字符串

update # set b=';'+b+';'

update # set b=replace(b,';','''')

update # set b=replace(b,',',''',''')

go

--创建中间表

create table #tmp(a int identity(1,1),co1 varchar(10),co2 varchar(10),co3 varchar(10),co4 varchar(10))

go

--如果#表中行数太多，可能会超过8000的长度，可以考虑用游标替换这部分

declare @sql varchar(8000)

set @sql=''

select @sql=@sql+b+' union all select ' from #

select @sql='select '+left(@sql,len(@sql)-17)

select @sql='insert into #tmp(co1,co2,co3,co4) '+@sql

print @sql

exec(@sql)

select * from #tmp

---------------------------------

select a,co1 from #tmp union all

select a,co2 from #tmp union all

select a,co3 from #tmp union all

select a,co4 from #tmp

order by a