-1

I'm trying to create a hierarchy from a text field I have in my table.

Visually it is relatively easy to see who is parent and child.

For example: (see a screenshot of the table results)

1M
-----1M5
----------1M5.5445
----------1M5.6
AC
-----AC
----------AC.1
----------AC.2
----------AC.3
AD
-----AD.1
-----AD.2
-----AD.3

Note that the dot in the string is not always present and constant.

How can I proceed in order to build this hierarchy of parent, child which can have several levels?

enter image description here

Microsoft SQL Server 2012 (SP2-GDR) (KB3194719) - 11.0.5388.0 (X64)

2
  • Help me write this query in SQL Mar 22, 2023 at 16:24
  • @AndriyM TYou understand the main goal here... You provide me a really good base. I will start from your code and optimise it with ID / row_number. Thank you very much.
    – Olivier
    Mar 23, 2023 at 16:13

1 Answer 1

0

Perhaps adding some sort of row ID would help with finding a more robust solution than this atrocity:

CREATE TABLE
  dbo.SomeData
(
  CODE_ACT varchar(50) NOT NULL PRIMARY KEY
);

INSERT INTO
  dbo.SomeData (CODE_ACT)
VALUES
  ('1M')
, ('1M5')
, ('1M5.5445')
, ('1M5.6')
, ('AT')
, ('AC')
, ('AC.1')
, ('AC.2')
, ('AC.3')
, ('AD')
, ('AD.1')
, ('AD.2')
, ('AD.3')
;
WITH
  Hierarchical AS
  (
    SELECT
      this.CODE_ACT
    , PARENT_CODE = x.CODE_ACT
    FROM
      dbo.SomeData AS this
      OUTER APPLY
      (
        SELECT TOP (1)
          that.CODE_ACT
        FROM
          dbo.SomeData AS that
        WHERE
          this.CODE_ACT LIKE that.CODE_ACT + '_%'
        ORDER BY
          that.CODE_ACT DESC
      ) AS x
  )
, Recursive AS
  (
    SELECT
      CODE_ACT
    , PARENT_CODE
    , Level = 0
    FROM
      Hierarchical
    WHERE
      PARENT_CODE IS NULL

    UNION ALL

    SELECT
      ch.CODE_ACT
    , ch.PARENT_CODE
    , par.Level + 1
    FROM
      Hierarchical AS ch
      INNER JOIN Recursive AS par ON ch.PARENT_CODE = par.CODE_ACT
  )
SELECT
  Item = REPLICATE('--', Level) + CODE_ACT
FROM
  Recursive
ORDER BY
  CODE_ACT
;

Output:

Item
1M
--1M5
----1M5.5445
----1M5.6
AC
--AC.1
--AC.2
--AC.3
AD
--AD.1
--AD.2
--AD.3
AT

Contributed by https://dba.stackexchange.com/users/6965

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.