怎么一行拆分成多行？

原始数据如下：
id tpye         value
1 类型1 aa,bb
2 类型2 aaa,bbb,ccc
3 类型3 aaa,bbb,ddd


想得到的结果如下：
id tpye         value
1 类型1 aa
1 类型1 bb
2 类型2 aaa
2 类型2 bbb
2 类型2 ccc
3 类型3 aaa
3 类型3 bbb
3 类型3 ddd

哪位大神能帮忙看下怎么写sql，非常感谢，数据库用的是2005。

7 个解决方案

#1

value没有规律 基本上无解

#2

USE [tempdb]
GO
IF OBJECT_ID('dbo.t') IS NOT NULL DROP TABLE dbo.t
GO
CREATE TABLE [dbo].[t](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[type] [nvarchar](max) NULL,
	[value] NVARCHAR(MAX) NULL
	PRIMARY KEY CLUSTERED 
	(
		[id] ASC
	)
)
INSERT INTO t([type],[value]) VALUES(N'类型1','aa,bb')
INSERT INTO t([type],[value]) VALUES(N'类型2','aaa,bbb,ccc')
INSERT INTO t([type],[value]) VALUES(N'类型3','aaa,bbb,ddd')
GO
IF OBJECT_ID('[dbo].[Fun_Split]') IS NOT NULL
DROP FUNCTION [dbo].[Fun_Split]
GO
-- =============================================
-- Author     :	yenange
-- Create date: 2014-03-04
-- Description:	切分字符串
-- Example    : SELECT * FROM [dbo].[Fun_Split]('a,b,d,c',',')
-- =============================================
CREATE FUNCTION [dbo].[Fun_Split]  
(  
    @str    NVARCHAR(MAX),  
    @separator  NVARCHAR(MAX)  
)
RETURNS TABLE 
AS
RETURN
(
	SELECT ROW_NUMBER() OVER (order by (select 0)) AS rowNum, B.id  
	FROM   (
			SELECT [value] = CONVERT(XML, '<v>' + REPLACE(@str, @separator, '</v><v>') + '</v>')  
		   ) A  
		   OUTER APPLY(  
		SELECT id = N.v.value('.', 'nvarchar(max)')  
		FROM   A.[value].nodes('/v') N(v)  
	) B
	WHERE ISNULL(B.id,'')!=''
)
GO
SELECT t.id,t.[type],fs.[id] AS [value] 
FROM dbo.t CROSS APPLY dbo.Fun_Split(t.[value],',') AS fs
/*
id	type	value
1	类型1	aa
1	类型1	bb
2	类型2	aaa
2	类型2	bbb
2	类型2	ccc
3	类型3	aaa
3	类型3	bbb
3	类型3	ddd
*/

#3

CREATE FUNCTION dbo.F_Split
 (
     @SplitString nvarchar(max),  --源字符串
     @Separator nvarchar(10)=' '  --分隔符号，默认为空格
 )
 RETURNS @SplitStringsTable TABLE  --输出的数据表
 (
     [id] int identity(1,1),
     [value] nvarchar(max)
 )
 AS
 BEGIN
     DECLARE @CurrentIndex int;
     DECLARE @NextIndex int;
     DECLARE @ReturnText nvarchar(max);

     SELECT @CurrentIndex=1;
     WHILE(@CurrentIndex<=len(@SplitString))
         BEGIN
             SELECT @NextIndex=charindex(@Separator,@SplitString,@CurrentIndex);
             IF(@NextIndex=0 OR @NextIndex IS NULL)
                 SELECT @NextIndex=len(@SplitString)+1;
                 SELECT @ReturnText=substring(@SplitString,@CurrentIndex,@NextIndex-@CurrentIndex);
                 INSERT INTO @SplitStringsTable([value]) VALUES(@ReturnText);
                 SELECT @CurrentIndex=@NextIndex+1;
             END
     RETURN;
 END
GO

--测试数据
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
Create table #T([id] int,[tpye] nvarchar(23),[value] nvarchar(31))
Insert #T
select 1,N'类型1',N'aa,bb' union all
select 2,N'类型2',N'aaa,bbb,ccc' union all
select 3,N'类型3',N'aaa,bbb,ddd'
Go
--测试数据结束
SELECT  id ,
        tpye ,
        t.value
FROM    #T
        CROSS APPLY ( SELECT    value
                      FROM      dbo.F_Split(value, ',')
                    ) t

#4

  CREATE function [dbo].[fn_splitString](@str nvarchar(50),@char nvarchar(2))
  returns @table table(column_str nvarchar(20))
  as
  begin
	declare @index int,@str_sub nvarchar(50)
	set @index = charindex(@char,@str) 
	while @index > 0
	begin
		set @str_sub = SUBSTRING(@str,1,@index-1)
		insert into @table select @str_sub
		set @str = SUBSTRING(@str,@index+1,LEN(@str)-charindex(@char,@str))
		set @index = charindex(@char,@str)
	end
	insert into @table select @str
	return 
  end


;WITH step01 AS (
SELECT 1 id ,'类型1' tpye,'aa,bb' value 
UNION ALL
SELECT 2 id ,'类型2' tpye,'aaa,bbb,ccc' value 
UNION ALL
SELECT 3 id ,'类型3' tpye,'aaa,bbb,ddd' value )

select * from step01 CROSS APPLY dbo.fn_splitString(step01.value,',') a

#5

用 wtih union all递归的方式提取，用stuff把提取的替换为空 这样

#6

SELECT A.ID,A.TYPE,
SUBSTRING(VALUE,NUMBER,CHARINDEX(',',VALUE+',',NUMBER)-NUMBER) AS VALUE
FROM 表 A
JOIN MASTER.DBO.SPT_VALUES B ON CHARINDEX(',',','+VALUE,NUMBER)=NUMBER
WHERE B.TYPE='P'

#7

字符串分割的方法就行，楼上都说了，我再举个另外实现方法的例子：

;with t(id,tpye,value) AS(
   select 0,N'类型0','aa' union all
   select 1,N'类型1','aa,bb' union all
   select 2,N'类型2','aaa,bbb,ccc'  union all
   select 3,N'类型3','aaa,bbb,ddd'
)
select t.*
      ,substring(t.value,sv.number+1,
           case when 
             charindex(',',t.value,sv.number+1)-sv.number<1 then 
              len(t.value) 
           else charindex(',',t.value,sv.number+1)-sv.number-1 end) 
from t
inner join master.dbo.spt_values as sv on sv.type='P' --and sv.number between 0 and len(value) 
and (substring(t.value,sv.number,1)=','  or sv.number in (0))
order by id
 

+----+------+-------------+-----+
| id | tpye | value       |     |
+----+------+-------------+-----+
| 0  | 类型0  | aa          | aa  |
| 1  | 类型1  | aa,bb       | aa  |
| 1  | 类型1  | aa,bb       | bb  |
| 2  | 类型2  | aaa,bbb,ccc | aaa |
| 2  | 类型2  | aaa,bbb,ccc | bbb |
| 2  | 类型2  | aaa,bbb,ccc | ccc |
| 3  | 类型3  | aaa,bbb,ddd | aaa |
| 3  | 类型3  | aaa,bbb,ddd | bbb |
| 3  | 类型3  | aaa,bbb,ddd | ddd |
+----+------+-------------+-----+