动态分行、动态分列(text数据类型的字段中,单元格包含分行字符)
动态分行、动态分列
1 --数据模拟请自行虚拟,本文为笔者使用需要从库中调取,为防止不必要的纠纷暂不提供数据 2 IF OBJECT_ID('tempdb..#f')IS NOT NULL DROP TABLE #f 3 SELECT TOP 10 ROW_NUMBER()OVER(ORDER BY l.id) 'rid' 4 ,l.id AS 'id' 5 ,l.data_225 AS 'data_225' 6 ,REPLACE(CAST(l.data_225 AS NVARCHAR(MAX)),CHAR(10),'') AS 'data225' 7 INTO #f 8 from 9 openquery(mysql,'SELECT * FROM flow_data_44 WHERE begin_time>=20150101;')l 10 WHERE CHARINDEX(CHAR(13),CAST(l.data_225 AS NVARCHAR(MAX)))>0 11 12 --原始数据 13 SELECT * FROM #f 14 15 DECLARE @i INT ,@n INT ,@id INT 16 DECLARE @tempdata VARCHAR(500) 17 DECLARE @scr VARCHAR(max) 18 SELECT @i=MIN(rid),@n=MAX(rid) FROM #f 19 20 --动态分行 21 IF OBJECT_ID('tempdb..#t')>0 DROP TABLE #t; 22 CREATE TABLE #t(rid INT 23 ,id INT 24 ,data225 NVARCHAR(max) 25 ,data225di NVARCHAR(max) 26 ) 27 WHILE(@i<=@n) 28 BEGIN 29 WHILE(EXISTS(SELECT 1 FROM #f WHERE ISNULL(data225,'')<>'' AND rid=@i)) 30 BEGIN 31 SELECT @tempdata=SUBSTRING(a.data225,1, CHARINDEX(CHAR(13),a.data225)) 32 ,@id=a.id 33 FROM #f a 34 WHERE rid=@i 35 36 INSERT INTO #t (rid,id,data225) 37 VALUES (@i,@id,@tempdata) 38 UPDATE #f SET data225=REPLACE(data225,@tempdata,'') WHERE rid=@i 39 END 40 SET @i=@i+1 41 END 42 --分行结果 43 SELECT * FROM #t 44 45 --动态分列 46 UPDATE #t SET data225di=REPLACE(REPLACE(data225,CHAR(10),''),CHAR(13),'') 47 SET @i=1 48 WHILE EXISTS(SELECT 1 FROM #t WHERE data225di<>'') 49 BEGIN 50 SET @scr='ALTER TABLE #t ADD col'+CONVERT(VARCHAR,@I)+' VARCHAR(200)' 51 EXEC(@scr) 52 53 SET @scr='declare @loc int update #t set @loc=charindex(''`'',data225di),col' 54 +convert(varchar,@i)+'=convert(varchar,case @loc when 0 then data225di else ' 55 +'substring(data225di,1,@loc-1) end),data225di=case @loc when 0 then '''' else ' 56 +'substring(data225di,@loc+1,len(data225di)-@loc) end where data225di<>''''' 57 58 exec(@scr) 59 SET @i=@i+1 60 END 61 62 SELECT * FROM #t
效果图预览: