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

Question

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

FL0XN0X

SSC Enthusiast

Points: 177
More actions
December 12, 2020 at 11:13 pm

#3819857

Hi, I need to build a hierachy multi-column table based on data in 1 column where the length of the string determines in which column the string must be placed. E.g.:
Column 1
A
A10
A10Q
A20
A30P
A30
Must result in
Column 1 Column 2 Column 3
A A10 A10Q
A A20
A A30 A30P
How to do this?
Many thanks in advance.
Kind regards,
Bart

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 1

FL0XN0X wrote:

Hi, I need to build a hierachy multi-column table based on data in 1 column where the length of the string determines in which column the string must be placed. E.g.:
Column 1
A
A10
A10Q
A20
A30P
A30
Must result in
Column 1 Column 2 Column 3
A A10 A10Q
A A20
A A30 A30P
How to do this?
Many thanks in advance.
Kind regards,
Bart

Is the implied level structure guaranteed? For example, if we look at A10Q, will the first character ALWAYS denote level one, the next two characters ALWAYS denote level 2, and the fourth character ALWAYS denote level 3?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

FL0XN0X SSC Enthusiast Points: 177 More actions · Answer 2

Hey,

The implied level structure is always:

1st level: 1 letter, eg. C

2nd level: 2 digits , eg. C03

3rd level: 1 letter, eg. C03C

4th level: 1 letter, eg. C03CA

5th level: 2 digits, eg. C03CA01

FL0XN0X SSC Enthusiast Points: 177 More actions · Answer 3

Found it :

SELECT

LEFT([ATC_KEY],1) AS [1st level]

,LEFT([ATC_KEY],3) AS [2nd level]

,LEFT([ATC_KEY],4) AS [3rd level]

....

jcelko212 32090 SSCrazy Eights Points: 9304 More actions · Answer 4

Will this regular expression validate (in part) your encoding scheme?

Foobar LIKE '[a-z][ 0-9][ 0-9][ a-z][ a-z][ 0-9][ 0-9]'

Please post DDL and follow ANSI/ISO standards when asking for help.

FL0XN0X SSC Enthusiast Points: 177 More actions · Answer 5

I was too enthousiastic. What I did was I only took the 7 character long records (WHERE LEN(column1)=7) and then applied the LEFT functions as indicatde above. But, apparently, some entries in column 1 doe ont have all levels of the hierachy. Some of them only have level1-2, others level 1-2-3, etc ....

Thus, any help still appreciated ....

Kind regards,

Bart

FL0XN0X SSC Enthusiast Points: 177 More actions · Answer 6

SELECT *

FROM [Staging].[3NF_ATCCOLLECTION] ATC

WHERE ATC.[ATC_KEY] LIKE '[a-z]'

OR ATC.[ATC_KEY] LIKE '[a-z][0-9][0-9]'

OR ATC.[ATC_KEY] LIKE '[a-z][0-9][0-9][a-z]'

OR ATC.[ATC_KEY] LIKE '[a-z][0-9][0-9][a-z][a-z]'

OR ATC.[ATC_KEY] LIKE '[a-z][0-9][0-9][a-z][a-z][0-9][0-9]'

ORDER BY ATC.[ATC_KEY]

This results in records that must be build as hierarchies so their length appear in columns

1st level (column) : 1 letter, eg. C

2nd level: 2 digits , eg. C03

3rd level: 1 letter, eg. C03C

4th level: 1 letter, eg. C03CA

5th level: 2 digits, eg. C03CA01

Please help ...

jcelko212 32090 SSCrazy Eights Points: 9304 More actions · Answer 7

I personally hate variable length codes. They make for messy screen displays and even worse printouts. However, you can pad the unused levels in the encoding with zeros or even blanks to assure that you have a fixed length encoding. It will make alphabetizing much easier too.

Please post DDL and follow ANSI/ISO standards when asking for help.

ScottPletcher SSC Guru Points: 101200 More actions · Answer 8

CREATE TABLE #3NF_ATCCOLLECTION 
    (
    ATC_KEY varchar(7) NOT NULL
    );
INSERT INTO #3NF_ATCCOLLECTION VALUES
    ('A10'), ('A10Q'), 
    ('A20'),
    ('A30P'), ('A30'),
    ('C'), ('C03'), ('C03C'), ('C03CA'), ('C03CA01') 


;WITH cte_get_max_lens AS (
    SELECT LEFT(ATC.ATC_KEY, 3) AS ATC_KEY_BASE, MAX(LEN(ATC.ATC_KEY)) AS ATC_KEY_MAX_LEN
    FROM #3NF_ATCCOLLECTION ATC
    WHERE LEN(ATC.ATC_KEY) >= 3
    GROUP BY LEFT(ATC.ATC_KEY, 3)
)
SELECT 
    LEFT(ATC.ATC_KEY, 1) AS Level1,
    SUBSTRING(ATC.ATC_KEY, 2, 2) AS Level2,
    CASE WHEN LEN(ATC.ATC_KEY) < 4 THEN '' ELSE SUBSTRING(ATC.ATC_KEY, 4, 1) END AS Level3,
    CASE WHEN LEN(ATC.ATC_KEY) < 5 THEN '' ELSE SUBSTRING(ATC.ATC_KEY, 5, 1) END AS Level4,
    CASE WHEN LEN(ATC.ATC_KEY) < 7 THEN '' ELSE SUBSTRING(ATC.ATC_KEY, 6, 2) END AS Level5
FROM #3NF_ATCCOLLECTION ATC
INNER JOIN cte_get_max_lens cgml ON ATC.ATC_KEY LIKE cgml.ATC_KEY_BASE + '%' AND 
    LEN(ATC.ATC_KEY) = cgml.ATC_KEY_MAX_LEN

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 9

FL0XN0X wrote:

SELECT * FROM [Staging].[3NF_ATCCOLLECTION] ATC WHERE ATC.[ATC_KEY] LIKE '[a-z]' OR ATC.[ATC_KEY] LIKE '[a-z][0-9][0-9]' OR ATC.[ATC_KEY] LIKE '[a-z][0-9][0-9][a-z]' OR ATC.[ATC_KEY] LIKE '[a-z][0-9][0-9][a-z][a-z]' OR ATC.[ATC_KEY] LIKE '[a-z][0-9][0-9][a-z][a-z][0-9][0-9]' ORDER BY ATC.[ATC_KEY]
This results in records that must be build as hierarchies so their length appear in columns
1st level (column) : 1 letter, eg. C
2nd level: 2 digits , eg. C03
3rd level: 1 letter, eg. C03C
4th level: 1 letter, eg. C03CA
5th level: 2 digits, eg. C03CA01
Please help ...

Not sure what you mean... can't you just use the LEN() function to do that? If that's not what you're looking for can you provide and example?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

FL0XN0X SSC Enthusiast Points: 177 More actions · Answer 10

@ Jeff Moden,

It concerns the hierchy as described in https://en.wikipedia.org/wiki/Anatomical_Therapeutic_Chemical_Classification_System

Now, this said, what I want to achieve is to have a Dimension which contains all hierarchies, and a fact table that contains this hierarchy as well fro each fact. This way, I can drill down the dimension by traversing each level of the hierarchy.

I'll give it a try this evening and see how far I can get and provde more detailed info if required.

Any hint is appreciated.

Kind regards,

Bart

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 11

FL0XN0X wrote:

@ Jeff Moden,
It concerns the hierchy as described in https://en.wikipedia.org/wiki/Anatomical_Therapeutic_Chemical_Classification_System
Now, this said, what I want to achieve is to have a Dimension which contains all hierarchies, and a fact table that contains this hierarchy as well fro each fact. This way, I can drill down the dimension by traversing each level of the hierarchy.
I'll give it a try this evening and see how far I can get and provde more detailed info if required.
Any hint is appreciated.
Kind regards,
Bart

Now THAT's some good info, Bart. I have to tell you, especially if you want some "drill down" capabilities, that having one column per level is probably not the way to go. I have an idea that may help a whole lot (actually, you might be amazed) but I need a wee bit more data that you were good enough to offer in any of your posts to make sure I do it right for you.

With that being said, would it be possible for you to attach a plain ol' text (PoT) file with a shedload of the codes (one per line, please) that you currently have in your database? Nothing fancy for formatting or anything. Just a "PoT" file of (hopefully all) the codes you're using with no other data.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 12

FL0XN0X wrote:

I was too enthousiastic. What I did was I only took the 7 character long records (WHERE LEN(column1)=7) and then applied the LEFT functions as indicatde above. But, apparently, some entries in column 1 doe ont have all levels of the hierachy. Some of them only have level1-2, others level 1-2-3, etc .... Thus, any help still appreciated ....
Kind regards,
Bart

Noted. I'm believe the method I'm thinking of will fix even that problem.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ScottPletcher SSC Guru Points: 101200 More actions · Answer 13

Jeff Moden wrote:

FL0XN0X wrote:
I was too enthousiastic. What I did was I only took the 7 character long records (WHERE LEN(column1)=7) and then applied the LEFT functions as indicatde above. But, apparently, some entries in column 1 doe ont have all levels of the hierachy. Some of them only have level1-2, others level 1-2-3, etc .... Thus, any help still appreciated ....
Kind regards,
Bart
Noted. I'm believe the method I'm thinking of will fix even that problem.

My code deals with that. Have you looked at it at all?!

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 14

ScottPletcher wrote:

Jeff Moden wrote:
FL0XN0X wrote:
I was too enthousiastic. What I did was I only took the 7 character long records (WHERE LEN(column1)=7) and then applied the LEFT functions as indicatde above. But, apparently, some entries in column 1 doe ont have all levels of the hierachy. Some of them only have level1-2, others level 1-2-3, etc .... Thus, any help still appreciated ....
Kind regards,
Bart
Noted. I'm believe the method I'm thinking of will fix even that problem.
My code deals with that. Have you looked at it at all?!

Yes... and no... it doesn't deal at all with what I'm thinking.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)