i have data in self-join hierarchical table continents have many countries have many regions have many states have many cities.
self-joining table structure:
|-------------------------------------------------------------| | id | name | type | parentid | istoplevel | |-------------------------------------------------------------| | 1 | north america | continent | null | 1 | | 12 | united states | country | 1 | 0 | | 113 | midwest | region | 12 | 0 | | 155 | kansas | state | 113 | 0 | | 225 | topeka | city | 155 | 0 | | 2 | south america | continent | null | 1 | | 22 | argentina | country | 2 | 0 | | 223 | southern | region | 22 | 0 | | 255 | la pampa | state | 223 | 0 | | 777 | santa rosa | city | 255 | 0 | |-------------------------------------------------------------| i have been able use recursive cte tree structure , depth of each node. failing using pivot create nice list of bottom locations , corresponding parents @ each level.
the expected results:
|------------------------------------------------------------------------------------| | continent | country | region | state | city | bottom_level_id | |------------------------------------------------------------------------------------| | north america | united states | midwest | kansas | topeka | 234 | | south america | argentina | southern | la pampa | santa rosa | 777 | |------------------------------------------------------------------------------------| there few key points should clarify.
every single entry has bottom level , top level. there no cases 5 types not present given location.
if filled out data, i'd have 50 entries north america @ state level, can imagine how immense table @ city level every continent on planet. billions of rows.
the reason necessity because need able join onto historical table of addresses person has lived at, , journey tree. figure if have locationid table, can left join onto view of query , nab appropriate columns.
this old database, 2005, , don't have sysadmin or control of schema.
my cte code
--cte ;with tree ( select id, name, parentid, type, 1 depth locationtable istoplevel = 1 union select l.id, l.name, l.parentid, l.type, t.depth+1 tree t join locationtable l on l.parentguid = t.guid ) good solid data, in useful format. got thinking , isn't table structure in format, why bother doing depth tree search if wasn't going join entries @ same time?
anyway, here rest.
the pivot attempt
;with tree ( select id, name, parentid, type locationtable istoplevel = 1 union select l.id, l.name, l.parentid, l.type tree t join locationtable l on l.parentguid = t.guid ) select * tree pivot ( max(name) type in ([continent],[country],[region],[state],[city]) ) pvt and have type in column, nulls else. have struggled before, need filter/join cte data before attempt pivot, have no idea start piece. have tried soooooooooo sloooooooow.
everytime think understand ctes , pivot, new makes me extremely humbled. please me. ; ;
if structure clean describe (no gaps, 5 levels always) might go easy way:
this data demands classical 1:n-table-tree, countries, states etc. live in own tables , link parent record
make sure there's index on parentid , id!
declare @tbl table(id int,name varchar(100),type varchar(100),parentid int,istoplevel bit); insert @tbl values (1,'north america','continent',null,1) ,(12,'united states','country',1,0) ,(113,'midwest','region',12,0) ,(155,'kansas','state',113,0) ,(225,'topeka','city',155,0) ,(2,'south america','continent',null,1) ,(22,'argentina','country',2,0) ,(223,'southern','region',22,0) ,(255,'la pampa','state',223,0) ,(777,'santa rosa','city',255,0); select level1.name continent ,level2.name country ,level3.name region ,level4.name state ,level5.name city ,level5.id bottom_level_id @tbl level1 inner join @tbl level2 on level1.id=level2.parentid inner join @tbl level3 on level2.id=level3.parentid inner join @tbl level4 on level3.id=level4.parentid inner join @tbl level5 on level4.id=level5.parentid level1.parentid null the result
continent country region state city bottom_level_id north america united states midwest kansas topeka 225 south america argentina southern la pampa santa rosa 777 
Comments
Post a Comment