Creating a hierarchy from text

Question

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?

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

Answer 1

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}