高手请帮忙？
a1.1,1.2,1.3,1.4,1.5
b2.1,2.2,2.3
d3.1,3.2
e4.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
