sql行转列或者一列转多列

--表
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'

7 个解决方案

#1

该回复于2014-12-08 11:16:06被管理员删除

#2

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

#3

这最好用动态转

#4

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

#5

;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

#6

提供一种方法

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

*/

#7

该回复于2014-12-09 09:15:05被管理员删除

#1