SQL分列语句

[zjdydlk1989]

求高手指导，能实现下面的过程的语句，用逗号对一个单元格分列成多个
原始数据：
id num
1 70, 72, 73, 81, 83, 84, 87, 89, 91, 92, 93
2 570, 571, 575, 579, 584, 589, 591, 592, 596
3 70, 72, 81, 87, 89
4 70, 81, 87, 89, 93
5 579, 591, 596
6 5, 80, 82, 84, 86, 89, 91
7 570, 573, 580, 583, 586, 587, 592, 594
8 5, 80, 82, 84, 86, 89, 91
9 570, 573, 580, 583, 586, 587, 592, 594
10 5, 80, 82, 84, 86, 89, 91
11 570, 573, 580, 583, 586, 587, 592, 594


希望结果：
id num1 num2 num3 num4 num5 num6 num7 num8 num9 num10 num11
1 70 72 73 81 83 84 87 89 91 92 93
2 570 571 575 579 584 589 591 592 596
3 70 72 81 87 89
4 70 81 87 89 93
5 579 591 596
6 5 80 82 84 86 89 91
7 570 573 580 583 586 587 592 594
8 5 80 82 84 86 89 91
9 570 573 580 583 586 587 592 594
10 5 80 82 84 86 89 91
11 570 573 580 583 586 587 592 594

[中国风]

num11--栏位是固定数还是要动态 取可用XML或自定义函数去拆分

[吉普赛的歌]

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,'')!=''
)

[中国风]

use Tempdb
go
--> --> 中国风(Roy)生成測試數據
 
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
Create table #T([id] int,[num] nvarchar(55))
Insert #T
select 1,N'70,72,73,81,83,84,87,89,91,92,93' union all
select 2,N'570,571,575,579,584,589,591,592,596' union all
select 3,N'70,72,81,87,89' union all
select 4,N'70,81,87,89,93' union all
select 5,N'579,591,596' union all
select 6,N'5,80,82,84,86,89,91' union all
select 7,N'570,573,580,583,586,587,592,594' union all
select 8,N'5,80,82,84,86,89,91' union all
select 9,N'570,573,580,583,586,587,592,594' union all
select 10,N'5,80,82,84,86,89,91' union all
select 11,N'570,573,580,583,586,587,592,594'
Go
DECLARE @i VARCHAR(3),@Sql NVARCHAR(max)='' 
SELECT TOP 1 @i=1+LEN(num)-LEN(REPLACE(num,',','')) FROM #T ORDER BY LEN(num)-LEN(REPLACE(num,',','')) DESC
WHILE @i>0
	SELECT @Sql=',[num'+@i+']=isnull(a.num.value(''(r/c)['+@i+']'',''varchar(10)''),'''')'+@Sql,@i=@i-1

EXEC('SELECT id'+@Sql+'
FROM 
(Select id,num=CONVERT(XML,''<r><c>''+REPLACE(num,'','',''</c><c>'')+''</c></r>'') from #T) AS a ')

/*
id	num1	num2	num3	num4	num5	num6	num7	num8	num9	num10	num11
1	70	72	73	81	83	84	87	89	91	92	93
2	570	571	575	579	584	589	591	592	596		
3	70	72	81	87	89						
4	70	81	87	89	93						
5	579	591	596								
6	5	80	82	84	86	89	91				
7	570	573	580	583	586	587	592	594			
8	5	80	82	84	86	89	91				
9	570	573	580	583	586	587	592	594			
10	5	80	82	84	86	89	91				
11	570	573	580	583	586	587	592	594			
*/