表 A
A_ID A_NAME
1 1,2,3,4
2 7,3,2,4表B
B_ID B_NAME
1 1
1 2
1 3
1 4
2 7
2 3
2 2
2 4
表A为原数据,表B为查询出来的数据
请问SQL应该怎么写?
A_ID A_NAME
1 1,2,3,4
2 7,3,2,4表B
B_ID B_NAME
1 1
1 2
1 3
1 4
2 7
2 3
2 2
2 4
表A为原数据,表B为查询出来的数据
请问SQL应该怎么写?
insert into tb
select 1,'1,2,3,4' union all
select 2,'7,3,2,4' create function f_name(@name varchar(20))
returns @tb table (name varchar(10))
begin
set @name=@name+','
while charindex(',',@name)>0
begin
insert into @tb select left(@name,charindex(',',@name)-1)
set @name=right(@name,len(@name)-charindex(',',@name))
end
return
endselect a.id,b.name from tb a cross apply f_name(a.name)b/*
id name
----------- ----------
1 1
1 2
1 3
1 4
2 7
2 3
2 2
2 4(8 row(s) affected)