--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)