求一段SQL语句 拆分某些字段,一行变多行

table1
a1 a2    a3
1  a      一
2  a,b    一,二
3  a,b,c  一,二,三

拆分成

table1
a1 a2  a3
1  a   一
2  a   一
2  b   二
3  a   一
3  b   二
3  c   三

7 个解决方案

#1

字符串分拆？

--各种字符串分函数

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_splitSTR]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_splitSTR]
GO

--3.2.1 循环截取法
CREATE FUNCTION f_splitSTR(
@s   varchar(8000),   --待分拆的字符串
@split varchar(10)     --数据分隔符
)RETURNS @re TABLE(col varchar(100))
AS
BEGIN
	DECLARE @splitlen int
	SET @splitlen=LEN(@split+'a')-2
	WHILE CHARINDEX(@split,@s)>0
	BEGIN
		INSERT @re VALUES(LEFT(@s,CHARINDEX(@split,@s)-1))
		SET @s=STUFF(@s,1,CHARINDEX(@split,@s)+@splitlen,'')
	END
	INSERT @re VALUES(@s)
	RETURN
END
GO


/*==============================================*/

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_splitSTR]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_splitSTR]
GO

--3.2.3.1 使用临时性分拆辅助表法
CREATE FUNCTION f_splitSTR(
@s   varchar(8000),  --待分拆的字符串
@split varchar(10)     --数据分隔符
)RETURNS @re TABLE(col varchar(100))
AS
BEGIN
	--创建分拆处理的辅助表(用户定义函数中只能操作表变量)
	DECLARE @t TABLE(ID int IDENTITY,b bit)
	INSERT @t(b) SELECT TOP 8000 0 FROM syscolumns a,syscolumns b

	INSERT @re SELECT SUBSTRING(@s,ID,CHARINDEX(@split,@s+@split,ID)-ID)
	FROM @t
	WHERE ID<=LEN(@s+'a') 
		AND CHARINDEX(@split,@split+@s,ID)=ID
	RETURN
END
GO

/*==============================================*/

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_splitSTR]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_splitSTR]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tb_splitSTR]') and objectproperty(id,N'IsUserTable')=1)
drop table [dbo].[tb_splitSTR]
GO

--3.2.3.2 使用永久性分拆辅助表法
--字符串分拆辅助表
SELECT TOP 8000 ID=IDENTITY(int,1,1) INTO dbo.tb_splitSTR
FROM syscolumns a,syscolumns b
GO

--字符串分拆处理函数
CREATE FUNCTION f_splitSTR(
@s     varchar(8000),  --待分拆的字符串
@split  varchar(10)     --数据分隔符
)RETURNS TABLE
AS
RETURN(
	SELECT col=CAST(SUBSTRING(@s,ID,CHARINDEX(@split,@s+@split,ID)-ID) as varchar(100))
	FROM tb_splitSTR
	WHERE ID<=LEN(@s+'a') 
		AND CHARINDEX(@split,@split+@s,ID)=ID)
GO


/*==============================================*/

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_splitSTR]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_splitSTR]
GO

--3.2.5 将数据项按数字与非数字再次拆份
CREATE FUNCTION f_splitSTR(
@s   varchar(8000),    --待分拆的字符串
@split varchar(10)     --数据分隔符
)RETURNS @re TABLE(No varchar(100),Value varchar(20))
AS
BEGIN
	--创建分拆处理的辅助表(用户定义函数中只能操作表变量)
	DECLARE @t TABLE(ID int IDENTITY,b bit)
	INSERT @t(b) SELECT TOP 8000 0 FROM syscolumns a,syscolumns b

	INSERT @re 
	SELECT	No=REVERSE(STUFF(col,1,PATINDEX('%[^-^.^0-9]%',col+'a')-1,'')),
		Value=REVERSE(LEFT(col,PATINDEX('%[^-^.^0-9]%',col+'a')-1))
	FROM(
		SELECT col=REVERSE(SUBSTRING(@s,ID,CHARINDEX(@split,@s+@split,ID)-ID))
		FROM @t
		WHERE ID<=LEN(@s+'a') 
			AND CHARINDEX(@split,@split+@s,ID)=ID)a
	RETURN
END
GO


/*==============================================*/

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_splitSTR]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_splitSTR]
GO

--3.2.6 分拆短信数据
CREATE FUNCTION f_splitSTR(@s varchar(8000))
RETURNS @re TABLE(split varchar(10),value varchar(100))
AS
BEGIN
	DECLARE @splits TABLE(split varchar(10),splitlen as LEN(split))
	INSERT @splits(split)
	SELECT 'AC' UNION ALL
	SELECT 'BC' UNION ALL
	SELECT 'CC' UNION ALL
	SELECT 'DC'	
	DECLARE @pos1 int,@pos2 int,@split varchar(10),@splitlen int
	SELECT TOP 1 
		@pos1=1,@split=split,@splitlen=splitlen
	FROM @splits
	WHERE @s LIKE split+'%'
	WHILE @pos1>0
	BEGIN
		SELECT TOP 1
			@pos2=CHARINDEX(split,@s,@splitlen+1)
		FROM @splits
		WHERE CHARINDEX(split,@s,@splitlen+1)>0
		ORDER BY CHARINDEX(split,@s,@splitlen+1)
		IF @@ROWCOUNT=0
		BEGIN
			INSERT @re VALUES(@split,STUFF(@s,1,@splitlen,''))
			RETURN
		END
		ELSE
		BEGIN
			INSERT @re VALUES(@split,SUBSTRING(@s,@splitlen+1,@pos2-@splitlen-1))
			SELECT TOP 1 
				@pos1=1,@split=split,@splitlen=splitlen,@s=STUFF(@s,1,@pos2-1,'')
			FROM @splits
			WHERE STUFF(@s,1,@pos2-1,'') LIKE split+'%'
		END
	END
	RETURN
END
GO

#2

DECLARE @a TABLE(a1 INT, a2 VARCHAR(20),   a3 VARCHAR(20)) 
insert @a select 1  ,'a',     '一' 
union all select 2  ,'a,b'    ,'一,二' 
union all select 3  ,'a,b,c'  ,'一,二,三' 

DECLARE @b TABLE(id INT IDENTITY,a INT DEFAULT 0)
INSERT @b SELECT TOP 100 0 FROM syscolumns s

SELECT a1, SUBSTRING(a2+',',id,CHARINDEX(',',a2+',',id+1)-id) a2,
	SUBSTRING(a3+',',id,CHARINDEX(',',a3+',',id+1)-id) a3
FROM @a a,@b b
WHERE SUBSTRING(','+a2,id,1)=','
ORDER BY 1 ,2
--result
/*
a1          a2                    a3                    
----------- --------------------- --------------------- 
1           a                     一
2           a                     一
2           b                     二
3           a                     一
3           b                     二
3           c                     三

（所影响的行数为 6 行）
*/

#3

--参考
拆分表:

--> --> (Roy)生成測試數據
 
if not object_id('Tab') is null
    drop table Tab
Go
Create table Tab([Col1] int,[COl2] nvarchar(5))
Insert Tab
select 1,N'a,b,c' union all
select 2,N'd,e' union all
select 3,N'f'
Go

SQL2000用辅助表:
if object_id('Tempdb..#Num') is not null
    drop table #Num
go
select top 100 ID=Identity(int,1,1) into #Num from syscolumns a,syscolumns b
Select 
    a.Col1,COl2=substring(a.Col2,b.ID,charindex(',',a.Col2+',',b.ID)-b.ID) 
from 
    Tab a,#Num b
where
    charindex(',',','+a.Col2,b.ID)=b.ID --也可用 substring(','+a.COl2,b.ID,1)=','


SQL2005用Xml:

select 
    a.COl1,b.Col2
from 
    (select Col1,COl2=convert(xml,'<root><v>'+replace(COl2,',','</v><v>')+'</v></root>') from Tab)a
outer apply
    (select Col2=C.v.value('.','nvarchar(100)') from a.COl2.nodes('/root/v')C(v))b




SQL05用CTE:

;with roy as 
(select Col1,COl2=cast(left(Col2,charindex(',',Col2+',')-1) as nvarchar(100)),Split=cast(stuff(COl2+',',1,charindex(',',Col2+','),'') as nvarchar(100)) from Tab
union all
select Col1,COl2=cast(left(Split,charindex(',',Split)-1) as nvarchar(100)),Split= cast(stuff(Split,1,charindex(',',Split),'') as nvarchar(100)) from Roy where split>''
)
select COl1,COl2 from roy order by COl1 option (MAXRECURSION 0)

生成结果:
/*
Col1        COl2
----------- -----
1           a
1           b
1           c
2           d
2           e
3           f
*/

#4

---------------------------------
--  Author: htl258(Tony)
--  Date  : 2009-08-13 09:53:53
---------------------------------
--> 生成测试数据表:table1

If not object_id('[table1]') is null
	Drop table [table1]
Go
Create table [table1]([a1] int,[a2] nvarchar(5),[a3] nvarchar(5))
Insert table1
Select 1,'a','一' union all
Select 2,'a,b','一,二' union all
Select 3,'a,b,c','一,二,三'
Go
--Select * from table1

-->SQL查询如下:
select b.a1, substring(b.a2+',',number,charindex(',',b.a2+',',number+1)-number) a2,
    substring(b.a3+',',number,charindex(',',b.a3+',',number+1)-number) a3
from master..spt_values a,[table1] b
where a.type='p' and substring(','+b.a2,number,1)=','

/*
a1          a2     a3
----------- ------ ------
1           a      一
2           a      一
2           b      二
3           a      一
3           b      二
3           c      三

(6 行受影响)

*/

#5

-------------------------------------
--  Author : liangCK 梁爱兰
--  Comment: 小梁 爱 兰儿
--  Date   : 2009-08-13 09:49:48
-------------------------------------
 
--> 生成测试数据: @T
DECLARE @T TABLE (a1 INT,a2 VARCHAR(5),a3 VARCHAR(8))
INSERT INTO @T
SELECT 1,'a','一' UNION ALL
SELECT 2,'a,b','一,二' UNION ALL
SELECT 3,'a,b,c','一,二,三'

--SQL查询如下:

;WITH Liang AS
(
	SELECT T.a1,T.a2,T.a3,
		A.x.value('.','varchar(20)') AS a4,T.a5,
        ROW_NUMBER() OVER(PARTITION BY T.a1 ORDER BY T.a1) AS rowid
	FROM (
		SELECT a1,a2,a3,a4=CONVERT(XML,'<v>'+REPLACE(a2,',','</v><v>')+'</v>'),
			 a5=CONVERT(XML,'<v>'+REPLACE(a3,',','</v><v>')+'</v>')
		FROM @T
	) AS T
		OUTER APPLY a4.nodes('//v') AS A(x)
)
SELECT a1,a2,a3,a4,
    a5=a5.value('(//v[position()=sql:column("rowid")])[1]','varchar(20)')
FROM Liang

/*
a1          a2    a3       a4                   a5
----------- ----- -------- -------------------- --------------------
1           a     一        a                    一
2           a,b   一,二      a                    一
2           a,b   一,二      b                    二
3           a,b,c 一,二,三    a                    一
3           a,b,c 一,二,三    b                    二
3           a,b,c 一,二,三    c                    三

(6 行受影响)
*/

#6

----------------------------------------------------------------
-- Author :fredrickhu(小F 向高手学习)
-- Date   :2009-08-13 09:55:02
----------------------------------------------------------------
--> 测试数据：[tbale1]
if object_id('[tbale1]') is not null drop table [tbale1]
go
create table [tbale1]([a1] int,[a2] varchar(5),[a3] varchar(8))
insert [tbale1]
select 1,'a','一' union all
select 2,'a,b','一,二' union all
select 3,'a,b,c','一,二,三'
--------------开始查询--------------------------
if object_id('Tempdb..#Num') is not null
    drop table #Num
go
select top 100 ID=Identity(int,1,1) into #Num from syscolumns a,syscolumns b
Select 
    a.a1 as col1,COl2=substring(a.a2,b.ID,charindex(',',a.a2+',',b.ID)-b.ID), 
           COL3=substring(a.a3,b.ID,charindex(',',a.a3+',',b.ID)-b.ID)
from 
    Tbale1 a,#Num b
where
    charindex(',',','+a.a2,b.ID)=b.ID 
----------------结果----------------------------
/*
col1        COl2  COL3     
----------- ----- -------- 
1           a     一
2           a     一
2           b     二
3           a     一
3           b     二
3           c     三

（所影响的行数为 6 行）
*/

#7

引用 3 楼 fredrickhu 的回复:

SQL code

--参考
拆分表:

--> --> (Roy)生成測試數據
 
if not object_id('Tab') is null
    drop table Tab
Go
Create table Tab([Col1] int,[COl2] nvarchar(5))
Insert Tab
select 1,N'a,b,c' union all
sel……

支持XML,特简单好用，就是看不明白真接COPY~