sql行转列或者一列转多列

[forget_loves]

--表
CREATE TABLE #table
(
id UNIQUEIDENTIFIER,
ST VARCHAR(100)
)
测试数据
INSERT INTO #table VALUES('9541B7C6-9649-4A87-B8F4-C77DCD4BBAB0','1,2,3,4,54,5,6,73')
INSERT INTO #table VALUES('C741E8DE-F2AA-4171-9984-92CC47D6F375','10,42,35,44,54,85,6y,')
INSERT INTO #table VALUES('C74A609C-5E85-49F5-81FA-CE46FDCBF613','15,24,3,4r,54,6r,735')
INSERT INTO #table VALUES('01AE5544-8B9B-4151-A5EC-06E3ADBF0F59','13,52,3,4y,5t4,5y,6t,t73')
实现效果
'9541B7C6-9649-4A87-B8F4-C77DCD4BBAB0','1','2','3','4','54','5','6','73'

[xiaodongni]

这不就是去了第一条数据吗？

[中国风]

这最好用动态转

[中国风]

是临时转还是表结构转多列

[Tiger_Zhao]

;WITH t1 AS ( -- 拆分st
    SELECT t.id,
           t.st,
           n.number,
           ROW_NUMBER() OVER(PARTITION BY id ORDER BY n.number) rn
      FROM #table t
      JOIN (SELECT number
              FROM master..spt_values
             WHERE type = 'p'
               AND number BETWEEN 1 AND 100
           ) n
        ON SUBSTRING(t.st+',',n.number,1) = ','
)
,t2 AS (
    SELECT t1.id,
           t1.rn,
           CASE WHEN t1.rn = 1 THEN
                LEFT(t1.st, t1.number-1)
           ELSE
                SUBSTRING(t1.st, t0.number+1, t1.number-t0.number-1)
           END st
      FROM t1
 LEFT JOIN t1 t0
        ON t0.id = t1.id
       AND t0.rn = t1.rn - 1
)
SELECT * 
  INTO #table2
  FROM t2

-- 动态查询
DECLARE @sql1 varchar(max)
DECLARE @sql2 varchar(max)
DECLARE @maxRn int
DECLARE @rn int
DECLARE @rnStr varchar(11)

SELECT @maxRn = MAX(rn) FROM #table2
SELECT @maxRn

SET @sql1 = 'SELECT t1.id, t1.st AS st1'

SET @sql2 = '
  FROM (SELECT * FROM #table2 WHERE rn=1) t1'

SET @rn = 2
WHILE @rn<= @maxRn
BEGIN
    SET @rnStr = Convert(varchar(11),@rn)

    SET @sql1 = @sql1 + ',
        t'+@rnStr+'.st AS st'+@rnStr

    SET @sql2 = @sql2 + '
        LEFT JOIN (SELECT * FROM #table2 WHERE rn='+@rnStr+') t'+@rnStr+'
               ON t'+@rnStr+'.id = t1.id'

    SET @rn = @rn+1
END

--PRINT @sql1 + @sql2
EXEC(@sql1 + @sql2)

id                                   st1  st2  st3  st4  st5  st6  st7  st8
------------------------------------ ---- ---- ---- ---- ---- ---- ---- ----
01AE5544-8B9B-4151-A5EC-06E3ADBF0F59 13   52   3    4y   5t4  5y   6t   t73
C741E8DE-F2AA-4171-9984-92CC47D6F375 10   42   35   44   54   85   6y   
9541B7C6-9649-4A87-B8F4-C77DCD4BBAB0 1    2    3    4    54   5    6    73
C74A609C-5E85-49F5-81FA-CE46FDCBF613 15   24   3    4r   54   6r   735  NULL

[中国风]

提供一种 方法

USE tempdb
GO
CREATE TABLE #table
 (
  id UNIQUEIDENTIFIER,
  ST VARCHAR(100)
 )
 INSERT INTO #table VALUES('9541B7C6-9649-4A87-B8F4-C77DCD4BBAB0','1,2,3,4,54,5,6,73')
 INSERT INTO #table VALUES('C741E8DE-F2AA-4171-9984-92CC47D6F375','10,42,35,44,54,85,6y,')
 INSERT INTO #table VALUES('C74A609C-5E85-49F5-81FA-CE46FDCBF613','15,24,3,4r,54,6r,735')
 INSERT INTO #table VALUES('01AE5544-8B9B-4151-A5EC-06E3ADBF0F59','13,52,3,4y,5t4,5y,6t,t73')
 GO
 CREATE FUNCTION fn_Split(@Str VARCHAR(100),@i TINYINT)
 RETURNS VARCHAR(100)
 AS
 BEGIN
	DECLARE @S1 VARCHAR(100)
	WHILE @i>1
		SELECT @Str=STUFF(@Str+',',1,CHARINDEX(',',@Str+','),''),@i=@i-1
	RETURN (LEFT(@Str+',',CHARINDEX(',',@Str+',')-1))	
 END
 go
DECLARE @Sql NVARCHAR(max),@ColCount TINYINT,@i TINYINT;
SELECT TOP 1 @ColCount=LEN(ST)-LEN(REPLACE(ST,',',''))+1 FROM #table ORDER BY LEN(ST)-LEN(REPLACE(ST,',','')) DESC
SELECT @Sql='',@i=1
WHILE @ColCount>=@i
	SELECT @Sql=@Sql+',dbo.fn_Split(ST,'+RTRIM(@i)+') as [ST'+RTRIM(@i)+']',@i=@i+1
EXEC('select ID,ST' +@Sql+' from #table')
	
	
/*
ID	ST	ST1	ST2	ST3	ST4	ST5	ST6	ST7	ST8
9541B7C6-9649-4A87-B8F4-C77DCD4BBAB0	1,2,3,4,54,5,6,73	1	2	3	4	54	5	6	73
C741E8DE-F2AA-4171-9984-92CC47D6F375	10,42,35,44,54,85,6y,	10	42	35	44	54	85	6y	
C74A609C-5E85-49F5-81FA-CE46FDCBF613	15,24,3,4r,54,6r,735	15	24	3	4r	54	6r	735	
01AE5544-8B9B-4151-A5EC-06E3ADBF0F59	13,52,3,4y,5t4,5y,6t,t73	13	52	3	4y	5t4	5y	6t	t73
*/