动态分行、动态分列（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

 

 

 

效果图预览：

转载于:https://www.cnblogs.com/sp-oh-dear/p/4228739.html