动态分行、动态分列（text数据类型的字段中，单元格包含分行字符）

--数据模拟请自行虚拟，本文为笔者使用需要从库中调取，为防止不必要的纠纷暂不提供数据
IF OBJECT_ID(‘tempdb..#f‘)>0 DROP TABLE #f
SELECT TOP 10 ROW_NUMBER()OVER(ORDER BY l.id) ‘rid‘
    ,l.id AS ‘id‘
    ,l.data_225 AS ‘data_225‘
    ,REPLACE(CAST(l.data_225 AS NVARCHAR(MAX)),CHAR(10),‘‘) AS ‘data225‘
INTO #f
 from 
openquery(mysql,‘SELECT * FROM flow_data_44 WHERE begin_time>=20150101;‘)l
WHERE  CHARINDEX(CHAR(13),CAST(l.data_225 AS NVARCHAR(MAX)))>0

--原始数据
SELECT * FROM #f

DECLARE @i INT ,@n INT ,@id INT
DECLARE @tempdata VARCHAR(500)
DECLARE @scr VARCHAR(max)
SELECT @i=MIN(rid),@n=MAX(rid) FROM #f

--动态分行
IF OBJECT_ID(‘tempdb..#t‘)>0 DROP TABLE #t;
CREATE TABLE #t(rid INT
    ,id INT
    ,data225 NVARCHAR(max)
    ,data225di NVARCHAR(max)
)
WHILE(@i<=@n)
BEGIN
    WHILE(EXISTS(SELECT 1 FROM #f WHERE ISNULL(data225,‘‘)<>‘‘ AND rid=@i))
    BEGIN
        SELECT @tempdata=SUBSTRING(a.data225,1, CHARINDEX(CHAR(13),a.data225))
            ,@id=a.id
        FROM #f a
        WHERE rid=@i
        
        INSERT INTO #t (rid,id,data225) 
        VALUES (@i,@id,@tempdata)        
        UPDATE #f SET data225=REPLACE(data225,@tempdata,‘‘) WHERE rid=@i        
    END
    SET @i=@i+1
END
--分行结果
SELECT * FROM #t

--动态分列
UPDATE #t SET data225di=REPLACE(REPLACE(data225,CHAR(10),‘‘),CHAR(13),‘‘)
SET @i=1
WHILE EXISTS(SELECT 1 FROM #t WHERE data225di<>‘‘)
BEGIN
    SET @scr=‘ALTER TABLE #t ADD col‘+CONVERT(VARCHAR,@I)+‘ VARCHAR(200)‘
    EXEC(@scr)
    
    SET @scr=‘declare @loc int update #t set @loc=charindex(‘‘`‘‘,data225di),col‘
    +convert(varchar,@i)+‘=convert(varchar,case @loc when 0 then data225di else ‘
    +‘substring(data225di,1,@loc-1) end),data225di=case @loc when 0 then ‘‘‘‘ else ‘
    +‘substring(data225di,@loc+1,len(data225di)-@loc) end  where data225di<>‘‘‘‘‘
    
    exec(@scr)
    SET @i=@i+1
END

SELECT * FROM #t