CODEWITHSUNDEEP
sqlsql-server
Matt Burland
Matt Burland

Reputation: 45135

Flatten Hierarchical Table

Follow up from here: Getting lowest level in a tree from any higher level with a self-join

I realized that I was actually asking the wrong question. I have a hierarchical table that looks something like this:

Type | Code  | Parent_Type | Parent_Code
  4  | 123   |     2       |     1
  4  | 234   |     2       |     1
  6  | 1234  |     4       |     123 
  6  | 2345  |     4       |     234
  7  | 12345 |     6       |     1234
  7  | 23456 |     6       |     1234
  7  | 34567 |     6       |     2345

It maps "type 2" codes to "type 4", "type 4" to "type 6" and "type 6" to "type 7". The previous question (and answer) dealt with how to select all the type 7 codes under any single parent code. So, for example, how to get all the type 7 codes under type 2, code 1.

But what I actually need to do is join this table with a list of types and codes. So for example, I might have a table:

Type | Code  
  4  | 123
  6  | 7851

And what I need to do is get all the level 7 codes under both those codes. In other words, (I think) I need to flatten the hierarchy into something like this:

Type | Code  | Parent_Type | Parent_Code
  7  | 12345 |     2       |     7
  7  | 23456 |     2       |     7
  7  | 34567 |     2       |     7
  7  | 12345 |     4       |     123
  7  | 23456 |     4       |     123
  7  | 34567 |     4       |     234
  7  | 12345 |     7       |     12345     // Note: these last three might not 
  7  | 23456 |     7       |     23456     // be strictly needed
  7  | 34567 |     7       |     34567

So then I could do something like:

 select p.type, p.code from myOtherTable o join mytable p on o.type = p.parent_type 
 and o.code = p.parent_code

To try and flatten the original table, I've tried some variations on the answer to my original question, but no really had much luck. For example:

with cte
as (
select p.type, p.code, p.parent_type, p.parent_code
from mytable as p
where parent_type = 2

union all

select c.type, c.code, c.parent_type, c.parent_code
from mytable as c
inner join cte on c.parent_code = cte.code
)
select *
from cte

Does nothing useful other than mess up the ordering of the table if I'd just directly called:

select * from mytable

Upvotes: 0

Views: 112

Answers (1)

Richard Deeming
Richard Deeming

Reputation: 31198

Sounds like a recursive CTE should do the trick:

WITH cteTree As
(
  SELECT
    T.Type,
    T.Code,
    T.Parent_Type,
    T.Parent_Code
  FROM
    RecordsToFind As F
    INNER JOIN Tree As T
    ON T.Type = F.Type
    And T.Code = F.Code

  UNION ALL

  SELECT
    T.Type,
    T.Code,
    T.Parent_Type,
    T.Parent_Code
  FROM
    cteTree As F
    INNER JOIN Tree As T
    ON T.Parent_Type = F.Type
    And T.Parent_Code = F.Code
  WHERE
    F.Type != 7
)
SELECT
  Type,
  Code,
  Parent_Type,
  Parent_Code
FROM
  cteTree
WHERE
  Type = 7
;

http://sqlfiddle.com/#!3/cc4ee/9

Upvotes: 1

Related Questions