August 20, 2018 at 2:25 am
Hello, I search the forum for similar cases but does not found a matching entry.
We have a result from table, that reflects a tree structure, but without tree children informations. The information was stored in the title with a codificaion xx.yy.zz means xx the first level, xx.yy 2nd level, xx.yy.zz third level in tree. My sql covers only 2 digit sections and only 2nd level. What I have today:
DECLARE @FirstTable TABLE (description varchar(50))
INSERT INTO @FirstTable VALUES ('10 TOP Level A')
INSERT INTO @FirstTable VALUES ('10.10 Sub Level A1')
INSERT INTO @FirstTable VALUES ('10.20 Sub Level A2')
INSERT INTO @FirstTable VALUES ('10.30 Sub Level A3')
INSERT INTO @FirstTable VALUES ('10.30.10 Sub Level A3-1')
INSERT INTO @FirstTable VALUES ('10.30.20 Sub Level A3-2')
INSERT INTO @FirstTable VALUES ('20 TOP Level B')
INSERT INTO @FirstTable VALUES ('20.10 Sub Level B1')
INSERT INTO @FirstTable VALUES ('20.20 Sub Level B2')
SELECT case when DESCRIPTION LIKE '[0-9][0-9] %' then DESCRIPTION when DESCRIPTION LIKE '[0-9][0-9].[0-9][0-9] %' then '|------ '+DESCRIPTION end as finaldesc FROM @FirstTable WHERE description like '[0-9][0-9] %' or description like '[0-9][0-9].[0-9][0-9] %' ORDER BY DESCRIPTION
go
10 TOP LEVEL A
10.10 Sub Level A1
10.20 Sub Level A2
10.20.10 Sub-Level A2-1
10.20.20 Sub Level A2-2
20 TOP Level B
20.10 Sub Level B1
20.20 Sub Level B2
20.100 Sub Level B10
Level 1 : No indent
Level 2 6 spaces indent
Level 3 9 spaces indent
How I have to modify my RegEx to cover this cases above to have a Tree-like view in the result? Hint: The order of the entries is already right, when I make a select to the table, don´t think about if the order of items in the result is right or not.
August 20, 2018 at 7:39 am
adiedler - Monday, August 20, 2018 2:25 AMHello, I search the forum for similar cases but does not found a matching entry.
We have a result from table, that reflects a tree structure, but without tree children informations. The information was stored in the title with a codificaion xx.yy.zz means xx the first level, xx.yy 2nd level, xx.yy.zz third level in tree. My sql covers only 2 digit sections and only 2nd level. What I have today:DECLARE @FirstTable TABLE (description varchar(50))
INSERT INTO @FirstTable VALUES ('10 TOP Level A')
INSERT INTO @FirstTable VALUES ('10.10 Sub Level A1')
INSERT INTO @FirstTable VALUES ('10.20 Sub Level A2')
INSERT INTO @FirstTable VALUES ('10.30 Sub Level A3')
INSERT INTO @FirstTable VALUES ('10.30.10 Sub Level A3-1')
INSERT INTO @FirstTable VALUES ('10.30.20 Sub Level A3-2')
INSERT INTO @FirstTable VALUES ('20 TOP Level B')
INSERT INTO @FirstTable VALUES ('20.10 Sub Level B1')
INSERT INTO @FirstTable VALUES ('20.20 Sub Level B2')
SELECT case when DESCRIPTION LIKE '[0-9][0-9] %' then DESCRIPTION when DESCRIPTION LIKE '[0-9][0-9].[0-9][0-9] %' then '|------ '+DESCRIPTION end as finaldesc FROM @FirstTable WHERE description like '[0-9][0-9] %' or description like '[0-9][0-9].[0-9][0-9] %' ORDER BY DESCRIPTION
go10 TOP LEVEL A
10.10 Sub Level A1
10.20 Sub Level A2
10.20.10 Sub-Level A2-1
10.20.20 Sub Level A2-2
20 TOP Level B
20.10 Sub Level B1
20.20 Sub Level B2
20.100 Sub Level B10
Level 1 : No indent
Level 2 6 spaces indent
Level 3 9 spaces indent
How I have to modify my RegEx to cover this cases above to have a Tree-like view in the result? Hint: The order of the entries is already right, when I make a select to the table, don´t think about if the order of items in the result is right or not.
You already have all of the information that you need to extend this. You're going to learn this much better and faster if you figure this out on your own.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 21, 2018 at 9:17 am
This works if there are up to 4 levels
select Case when C4 is not null
then Space(12)
when C3 is not null
then Space(9)
when C2 is not null
then Space(6)
else ''
end + Description
from (
select Description,
convert(varchar(20), ParseName(Description, 1)) c1,
convert(varchar(20), ParseName(Description, 2)) c2,
convert(varchar(20), ParseName(Description, 3)) c3,
convert(varchar(20), ParseName(Description, 4)) c4
from @FirstTable
) v
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply