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;
