I have a hierarchy table with a path enumeration column (lineage column)

mytable:

desired result:

How can I produce the desired result table using a recursive CTE assuming it is the best route to take?

SQL statements to reproduce the table:

create table dbo.#mytable (
    [path] VARCHAR(13),
    [desc] VARCHAR(70),
    [lvl] SMALLINT
)

insert into #mytable([path],[desc],[lvl]) 
values('D','Chicago',1),('DADB','Los Angeles',2),('DADB761','Miami',3),('DADB761G93','Detroit',4),
('DADB761G93276','San Francisco',5),('DADB761G93277','Seattle',5)

CodePudding user response：

Usually you would build the [path] column using data from multiple rows in a recursive CTE. In this case, you are "flattening" out a multi-row table. This is not automatically scalable if you have more than 5 levels. You'll have to add a column for each level. With that said, you can dowhat you're asking like this:

SQL Fiddle

MS SQL Server 2017 Schema Setup:

CREATE TABLE mytable (
    [path] VARCHAR(13) null,
    [desc] VARCHAR(70) null,
    [lvl] SMALLINT null
)

INSERT INTO mytable ([path],[desc],[lvl]) 
VALUES
  ('D','Chicago',1)
  ,('DADB','Los Angeles',2)
  ,('DADB761','Miami',3)
  ,('DADB761G93','Detroit',4)
  ,('DADB761G93276','San Francisco',5)
  ,('DADB761G93277','Seattle',5)

Query 1:

WITH destruct AS (

  SELECT DISTINCT 
    CAST([path] as nvarchar(512)) as code1
    , CAST('' as nvarchar(512)) as code2
    , CAST('' as nvarchar(512)) as code3
    , CAST('' as nvarchar(512)) as code4
    , CAST('' as nvarchar(512)) as code5
    , CAST([desc] as nvarchar(512)) as desc1
    , CAST('' as nvarchar(512)) as desc2
    , CAST('' as nvarchar(512)) as desc3
    , CAST('' as nvarchar(512)) as desc4
    , CAST('' as nvarchar(512)) as desc5
    , mt.[path]
    , mt.lvl
  FROM mytable as mt
  WHERE lvl = 1

  UNION ALL

  SELECT
    CAST(d.code1 as nvarchar(512)) as code1
    , CAST(CASE WHEN d.lvl   1 = 2 THEN RIGHT(mt.[path], LEN(mt.[path])-LEN(d.[path])) ELSE d.code2 END as nvarchar(512)) as code2
    , CAST(CASE WHEN d.lvl   1 = 3 THEN RIGHT(mt.[path], LEN(mt.[path])-LEN(d.[path])) ELSE d.code3 END as nvarchar(512)) as code3
    , CAST(CASE WHEN d.lvl   1 = 4 THEN RIGHT(mt.[path], LEN(mt.[path])-LEN(d.[path])) ELSE d.code4 END as nvarchar(512)) as code4
    , CAST(CASE WHEN d.lvl   1 = 5 THEN RIGHT(mt.[path], LEN(mt.[path])-LEN(d.[path])) ELSE d.code5 END as nvarchar(512)) as code5
    , CAST(d.desc1 as nvarchar(512)) as desc1
    , CAST(CASE WHEN d.lvl   1 = 2 THEN mt.[desc] ELSE d.desc2 END as nvarchar(512)) as desc2
    , CAST(CASE WHEN d.lvl   1 = 3 THEN mt.[desc] ELSE d.desc3 END as nvarchar(512)) as desc3
    , CAST(CASE WHEN d.lvl   1 = 4 THEN mt.[desc] ELSE d.desc4 END as nvarchar(512)) as desc4
    , CAST(CASE WHEN d.lvl   1 = 5 THEN mt.[desc] ELSE d.desc5 END as nvarchar(512)) as desc5
    , mt.[path]
    , mt.lvl
  FROM destruct as d
    INNER JOIN mytable as mt
      ON mt.lvl = d.lvl   1
      AND mt.[path] like d.[path]   '%'
  AND d.lvl < 20  --Safety for debugging. Remove in Production.
)
SELECT *
FROM destruct as d
WHERE d.lvl = 5

Results:

| code1 | code2 | code3 | code4 | code5 |   desc1 |       desc2 | desc3 |   desc4 |         desc5 |          path | lvl |
|-------|-------|-------|-------|-------|---------|-------------|-------|---------|---------------|---------------|-----|
|     D |   ADB |   761 |   G93 |   276 | Chicago | Los Angeles | Miami | Detroit | San Francisco | DADB761G93276 |   5 |
|     D |   ADB |   761 |   G93 |   277 | Chicago | Los Angeles | Miami | Detroit |       Seattle | DADB761G93277 |   5 |

Note: all the CAST expressions are to ensure the root and sub queries have the same datatype. SQL Fiddle was throwing errors about type mismatches without them.