Dynamic branch, dynamic column (the Text data type field, the cell contains the branch character)

--Data Simulation Please self-virtual, this article for the author use needs to be transferred from the library, in order to prevent unnecessary disputes temporarily do not provide dataIF object_id('tempdb: #f')>0 DROP TABLE#fSELECT TOP TenRow_number () Over(ORDER  byL.id)'RID', L.id as 'ID', l.data_225 as 'data_225'    ,REPLACE(CAST(l.data_225 as NVARCHAR(MAX)),CHAR(Ten),"') as 'data225' into#f from OpenQuery(MySQL,'SELECT * from flow_data_44 WHERE begin_time>=20150101;') LWHERE  CHARINDEX(CHAR( -),CAST(l.data_225 as NVARCHAR(MAX)))>0--Raw DataSELECT *  from#fDECLARE @i INT,@n INT,@id INTDECLARE @tempdata VARCHAR( -)DECLARE @scr VARCHAR(Max)SELECT @i=MIN(RID),@n=MAX(RID) from#f--Dynamic BranchIF object_id('tempdb: #t')>0 DROP TABLE#t;CREATE TABLE#t (RIDINT, idINT, data225NVARCHAR(Max), Data225diNVARCHAR(Max)) while(@i<=@n)BEGIN     while(EXISTS(SELECT 1  from#fWHERE ISNULL(data225,"')<>"'  andRid=@i))    BEGIN        SELECT @tempdata=SUBSTRING(a.data225,1,CHARINDEX(CHAR( -), a.data225),@id=a.id from#f aWHERERid=@i                INSERT  into#t (rid,id,data225)VALUES(@i,@id,@tempdata)                UPDATE#fSETdata225=REPLACE(data225,@tempdata,"')WHERERid=@i            END    SET @i=@i+1END--Branch ResultsSELECT *  from#t--Dynamic disaggregationUPDATE#tSETData225di=REPLACE(REPLACE(data225,CHAR(Ten),"'),CHAR( -),"')SET @i=1 while EXISTS(SELECT 1  from#tWHEREData225di<>"')BEGIN    SET @scr='ALTER TABLE #t ADD Col'+CONVERT(VARCHAR,@i)+'VARCHAR ($)'    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) [email protected]) end where data225di<>" '"        exec(@scr)    SET @i=@i+1ENDSELECT *  from#t

Code, expand

Preview:

Dynamic branch, dynamic column (the Text data type field, the cell contains the branch character)