Build hierarchy multi-column table on data in 1 column based on string length

• naumon765

  Grasshopper

  Points: 15

  More actions

  May 23, 2026 at 4:32 pm

  #4847303

  hi hope this helps

  SAMPLE DATA

  DROP TABLE IF EXISTS dbo.SampleData;

  GO

  CREATE TABLE dbo.SampleData

  (

  Col1 VARCHAR(20)

  );

  GO

  INSERT INTO dbo.SampleData (Col1)

  VALUES

  ('A'),

  ('B'),

  ('A10'),

  ('A20'),

  ('B05'),

  ('A10Q'),

  ('A30P'),

  ('B05X'),

  ('A10QA'),

  ('B05XY'),

  ('A10QA01'),

  ('A10QA02'),

  ('B05XY99'),

  ('C'),

  ('C03'),

  ('C03C'),

  ('C03CA'),

  ('C03CA01'),

  ('D07'),

  ('D07M'),

  ('E99ZZ01');

  GO

   

  T-SQL solution

  SELECT

     Col1,

        CASE WHEN LEN(Col1) >= 1 THEN LEFT(Col1,1) END AS Level1,

       CASE WHEN LEN(Col1) >= 3 THEN LEFT(Col1,3) END AS Level2,

       CASE WHEN LEN(Col1) >= 4 THEN LEFT(Col1,4) END AS Level3,

       CASE WHEN LEN(Col1) >= 5 THEN LEFT(Col1,5) END AS Level4,

       CASE WHEN LEN(Col1) >= 7 THEN LEFT(Col1,7) END AS Level5

  FROM

       dbo.SampleData

  ORDER BY

        Col1;

  GO