SQL按分隔字元拆分欄位串

標籤：sele   HERE   des   nio   color   bsp   split   business   var   

CREATE   VIEW [dbo].[Split_BusinessUnit]AS    WITH tt    AS (       SELECT BusinessUnit.BusinessUnitId            , BusinessUnit.BusinessUnitId zz_id            , BusinessUnit.new_name            , BusinessUnit.ParentBusinessUnitId            , 1 lev       FROM   BusinessUnit       WHERE  IsDisabled = 0       UNION ALL       SELECT tt.BusinessUnitId            , b.BusinessUnitId            , b.new_name            , b.ParentBusinessUnitId            , lev + 1       FROM   tt              JOIN BusinessUnit AS b                  ON tt.ParentBusinessUnitId = b.BusinessUnitId       WHERE  IsDisabled = 0 )       , ttt    AS ( SELECT BusinessUnitId              , CAST(zz_id AS VARCHAR(60)) zz_id              , new_name              , ROW_NUMBER() OVER ( PARTITION BY BusinessUnitId                                    ORDER BY lev DESC ) lev         FROM   tt )    SELECT   BusinessUnitId           , MAX(lev) lev           , MAX(CASE WHEN lev = 1 THEN zz_id                      ELSE NULL                 END) lev1id           , MAX(CASE WHEN lev = 1 THEN new_name                      ELSE NULL                 END) lev1           , MAX(CASE WHEN lev = 2 THEN zz_id                      ELSE NULL                 END) lev2id           , MAX(CASE WHEN lev = 2 THEN new_name                      ELSE NULL                 END) lev2           , MAX(CASE WHEN lev = 3 THEN zz_id                      ELSE NULL                 END) lev3id           , MAX(CASE WHEN lev = 3 THEN new_name                      ELSE NULL                 END) lev3           , MAX(CASE WHEN lev = 4 THEN zz_id                      ELSE NULL                 END) lev4id           , MAX(CASE WHEN lev = 4 THEN new_name                      ELSE NULL                 END) lev4           , MAX(CASE WHEN lev = 5 THEN zz_id                      ELSE NULL                 END) lev5id           , MAX(CASE WHEN lev = 5 THEN new_name                      ELSE NULL                 END) lev5           , MAX(CASE WHEN lev = 6 THEN zz_id                      ELSE NULL                 END) lev6id           , MAX(CASE WHEN lev = 6 THEN new_name                      ELSE NULL                 END) lev6           , MAX(CASE WHEN lev = 7 THEN zz_id                      ELSE NULL                 END) lev7id           , MAX(CASE WHEN lev = 7 THEN new_name                      ELSE NULL                 END) lev7           , MAX(CASE WHEN lev = 8 THEN zz_id                      ELSE NULL                 END) lev8id           , MAX(CASE WHEN lev = 8 THEN new_name                      ELSE NULL                 END) lev8           , MAX(CASE WHEN lev = 9 THEN zz_id                      ELSE NULL                 END) lev9id           , MAX(CASE WHEN lev = 9 THEN new_name                      ELSE NULL                 END) lev9           , MAX(CASE WHEN lev = 10 THEN zz_id                      ELSE NULL                 END) lev10id           , MAX(CASE WHEN lev = 10 THEN new_name                      ELSE NULL                 END) lev10    FROM     ttt    GROUP BY BusinessUnitId;

 

SQL按分隔字元拆分欄位串