sql如何将一条内容包含空格的记录拆分成多条记录

各位大侠，请帮忙！

我有一表单：

ID  CODE1  CODE2
1    AA     AA BB CC

我如何写SQL语句可以把表单变成：

ID  CODE1  CODE2
1   AA     AA
2   AA     BB
3   AA     CC

谢谢各位！

4 个解决方案

#1

--> 测试数据：[数据库]
if object_id('[数据库]') is not null drop table [数据库]
create table [数据库](
[编号] int,
[字段1] varchar(8),
[字段2] varchar(11)
)
insert [数据库]
select 1,'AA|BB|CC','888|999|666' union all
select 2,'AA|BB|CC','111|222|333' union all
select 3,'AA|BB|CC','444|555|666'

with T as 
(
    select [编号],charindex('|','|'+[字段1])col1a,
    charindex('|',[字段1]+'|')+1 col1b,
    charindex('|','|'+[字段2]) col2a,
    charindex('|',[字段2]+'|')+1 col2b 
    from [数据库]
    union all
    select a.编号,b.col1b,charindex('|',[字段1]+'|',b.col1b)+1,
    b.col2b,charindex('|',[字段2]+'|',b.col2b)+1
    from [数据库] a join T b on a.编号=b.编号 
    where charindex('|',[字段1]+'|',b.col1b)>0
          or charindex('|',[字段2]+'|',b.col2b)>0
)
select 
a.编号,[字段1]=substring(a.[字段1]+'|',b.col1a,b.col1b - b.col1a - 1),
[字段2]=substring(a.[字段2]+'|',b.col2a,b.col2b - b.col2a - 1) into #test
 from [数据库] a join T b on a.编号=b.编号 
 order by 1
 

declare @str varchar(2000)
set @str=''
select 
     @str=@str+','+[字段1]+'=max(case when [字段1]='
     +quotename([字段1],'''')+' then [字段2] else 0 end)'
from 
     #test
group by 
      [字段1]     
exec('select 编号'+@str+' from #test group by 编号')
/*
编号	AA	BB	CC
-------------------------
1	888	999	666
2	111	222	333
3	444	555	666
*/

--一个类似的例子

#2

--> 测试数据：[test]
if object_id('[test]') is not null 
drop table [test]
create table [test](
[ID] int,
[CODE1] varchar(2),
[CODE2] varchar(10)
)
insert [test]
select 1,'AA','AA BB CC' union all
select 2,'BB','FF EE DD'

with T (id,[CODE1],P1,P2) as
(
    select 
		id,
		[CODE1],
		charindex(' ',' '+[CODE2]),
		charindex(' ',[CODE2]+' ')+1 
	from 
		test
    union all
    select 
		a.id,
		a.CODE1,
		b.P2,
		charindex(' ',[CODE2]+' ',b.P2)+1 
	from 
		test  a 
	join 
		T b 
	on 
		a.id=b.id 
	where 
		charindex(' ',[CODE2]+' ',b.P2)>0
)
select 
	a.id,
	a.CODE1,
	name=substring(a.[CODE2]+' ',b.P1,b.P2 - b.P1 - 1) 
from 
	test a 
join 
	T b 
on 
	a.id=b.id 
order by 
	1
/*
id	CODE1	name
--------------------------
1	AA	AA
1	AA	BB
1	AA	CC
2	BB	FF
2	BB	EE
2	BB	DD
*/

#3

--> 测试数据：[tb]
IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]
GO 
CREATE TABLE [tb]([ID] INT,[CODE1] VARCHAR(2),[CODE2] VARCHAR(8))
INSERT [tb]
SELECT 1,'AA','AA BB CC'
--------------开始查询--------------------------
SELECT  a.[ID], a.[CODE1], b.[CODE2]
FROM    (
          SELECT id, [CODE1], [CODE2]= CAST( '<x>'+ REPLACE ([CODE2], ' ', '</x><x>')+ '</x>'  AS XML) FROM [tb]
        ) a
OUTER APPLY (
              SELECT [CODE2]= N.v.value ('.', 'varchar(10)') FROM a.[CODE2].nodes ('/x') N( v)
            ) b
/*
ID          CODE1 CODE2
----------- ----- ----------
1           AA    AA
1           AA    BB
1           AA    CC

(3 行受影响)


*/

#4

引用 3 楼 筱筱澄 的回复:

--> 测试数据：[tb]
IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]
GO 
CREATE TABLE [tb]([ID] INT,[CODE1] VARCHAR(2),[CODE2] VARCHAR(8))
INSERT [tb]
SELECT 1,'AA','AA BB CC'
--------------开始查询--------------------------
SELECT  a.[ID], a.[CODE1], b.[CODE2]
FROM    (
          SELECT id, [CODE1], [CODE2]= CAST( '<x>'+ REPLACE ([CODE2], ' ', '</x><x>')+ '</x>'  AS XML) FROM [tb]
        ) a
OUTER APPLY (
              SELECT [CODE2]= N.v.value ('.', 'varchar(10)') FROM a.[CODE2].nodes ('/x') N( v)
            ) b
/*
ID          CODE1 CODE2
----------- ----- ----------
1           AA    AA
1           AA    BB
1           AA    CC

(3 行受影响)


*/

很好