高手请帮忙？
a 1.1,1.2,1.3,1.4,1.5
b 2.1,2.2,2.3
d 3.1,3.2
e 4.1,4.2


如何把以上的数据转为(1.1,1.2,1.3,1.4,1.5   是规定的5列)

row           row1       row2         row3       row4         row5
----------------------------------------------
a               1.1         1.2           1.3         1.4           1.5
b               2.1         2.2           2.3         0               0  
c               3.1         3.2           0             0               0  
d               4.1         4.2           0             0               0

------解决方案--------------------
CREATE TABLE A
(
col1 varchar(10),
col2 varchar(50)
)
INSERT INTO A
SELECT 'a ', '1.1,1.2,1.3,1.4,1.5 ' UNION ALL
SELECT 'b ', '2.1,2.2,2.3 ' UNION ALL
SELECT 'd ', '3.1,3.2 ' UNION ALL
SELECT 'e ', '4.1,4.2 '
GO
DECLARE @i int,@s VARCHAR(1000)
SET @i=0
SELECT col1,col2 INTO #t FROM A
WHILE @@ROWCOUNT> 0
BEGIN
SELECT @i=@i+1,@s= 'ALTER TABLE #t ADD col2 '+CAST(@i as varchar)+ ' VARCHAR(10) '
EXEC(@s)
SET @s= ' UPDATE #t SET col2 '+CAST(@i as varchar)+ ' =LEFT(col2,CHARINDEX( ' ', ' ',col2+ ' ', ' ')-1),col2=STUFF(col2,1,CHARINDEX( ' ', ' ',col2+ ' ', ' '), ' ' ' ') WHERE col2 > ' ' ' ' '
EXEC(@s)
END
SET @s= 'ALTER TABLE #t DROP COLUMN col2,col2 '+CAST(@i AS VARCHAR)
EXEC(@s)
SELECT * FROM #t
DROP TABLE #t
DROP TABLE A
--
col1 col21 col22 col23 col24 col25
---------- ---------- ---------- ---------- ---------- ----------
a 1.1 1.2 1.3 1.4 1.5
b 2.1 2.2 2.3 NULL NULL
d 3.1 3.2 NULL NULL NULL
e 4.1 4.2 NULL NULL NULL

(4 行受影响)

------解决方案--------------------
select row=r1,
row1=substring(r2,1,3),
row2=substring(r2,5,3),
row3=(case when len(rtrim(r2))> 8 then substring(r2,9,3) else '0 ' end),
row4=(case when len(rtrim(r2))> 12 then substring(r2,13,3) else '0 ' end),
row5=(case when len(rtrim(r2))> 16 then substring(r2,17,3) else '0 ' end)
from tt