ASCII Tree in SQL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

ASCII Tree in SQL

Author

Topic

Kristen
Test

22859 Posts

Posted - 2010-05-28 : 03:30:45

I would like to output this:


1.1
|--2.1
|  |--3.1
|  |--3.2
|  |  |--4.1
|  |  `--4.2
|  `--3.3
|     `--4.3
|--2.2
`--2.3
   `--3.4

here's my data:


DECLARE	@tblTree TABLE
(
	[Sequence]	int NOT NULL,
	[Level]		int NOT NULL,
	[Value]		varchar(10) NOT NULL,
	PRIMARY KEY
	(
		[Sequence]
	)
)

INSERT INTO @tblTree
SELECT *
FROM
(
SELECT	[Sequence] = 1, [Level] = 1, [Value] = '1.1' UNION ALL
SELECT	2, 2, '2.1' UNION ALL
SELECT	3, 3, '3.1' UNION ALL
SELECT	4, 3, '3.2' UNION ALL
SELECT	5, 4, '4.1' UNION ALL
SELECT	6, 4, '4.2' UNION ALL
SELECT	7, 3, '3.3' UNION ALL
SELECT	8, 4, '4.3' UNION ALL
SELECT	9, 2, '2.2' UNION ALL
SELECT	10, 2, '2.3' UNION ALL
SELECT	11, 3, '3.4'
) AS T
ORDER BY [Sequence]

SELECT
--	*,
	REPLICATE('|  ', [Level]-1) + [Value]
FROM	@tblTree
ORDER BY [Sequence]

exact style of the tree doesn't matter, so if a different style makes it easier to generate the output that's fine.

Thanks!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-05-28 : 03:38:17

You know what madhivanan would say?

No, you're never too old to Yak'n'Roll if you're too young to die.

Kristen
Test

22859 Posts

Posted - 2010-05-28 : 05:11:04

I'm hoping he'll enjoy the SQL challenge, but if not Classic ASP / VBScript code please

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-05-28 : 07:51:39

It must be just me, I stare at the thread for over 5 mins and still can't figure out what do you want

What is it that you need really ? You already have the query that generates the required output.

KH
[spoiler]Time is always against us[/spoiler]

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-05-28 : 07:53:51

Not really.
There differences between wanted result and output of the given query.

No, you're never too old to Yak'n'Roll if you're too young to die.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-05-28 : 08:06:19

quote:
Originally posted by webfred

Not really.
There differences between wanted result and output of the given query.

No, you're never too old to Yak'n'Roll if you're too young to die.

Thanks for the pointer but i still don't see any different in the required and the output of the query

. It is Friday night and must be that

i had just now. I think i should go to bed early

KH
[spoiler]Time is always against us[/spoiler]

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-05-28 : 08:20:26

You can print both results on transparent papers then put the pages on top of each other and then, if there is enough light, you will see the difference, I am sure.

No, you're never too old to Yak'n'Roll if you're too young to die.

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2010-05-28 : 09:30:11

for each row, call xp_cmdshell with mkdir to make a folder with the right name in the right location. Then open explorer, take a screenshot, print it on durable acid-free paper, and voila!

elsasoft.org

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-05-28 : 09:40:45

quote:
Originally posted by jezemine

for each row, call xp_cmdshell with mkdir to make a folder with the right name in the right location. Then open explorer, take a screenshot, print it on durable acid-free paper, and voila!

elsasoft.org

WOW!
That rocks!

No, you're never too old to Yak'n'Roll if you're too young to die.

Kristen
Test

22859 Posts

Posted - 2010-05-28 : 09:55:51

"but i still don't see any different in the required and the output of the query"

I think you are winding me up!

"must be that i had just now"

I doubt it

Here are the two, interspersed


1.1
1.1
|--2.1
|  2.1
|  |--3.1
|  |  3.1
|  |--3.2
|  |  3.2
|  |  |--4.1
|  |  |  4.1
|  |  `--4.2
|  |  |  4.2
|  `--3.3
|  |  3.3
|     `--4.4
|  |  |  4.4
|--2.2
|  2.2
`--2.3
|  2.3
   `--3.3
|  |  3.3

1.1
|--2.1
|  |--3.1
|  |--3.2
|  |  |--4.1
|  |  `--4.2
|  `--3.3
|     `--4.4
|--2.2
`--2.3
   `--3.3

1.1
|  2.1
|  |  3.1
|  |  3.2
|  |  |  4.1
|  |  |  4.2
|  |  3.3
|  |  |  4.4
|  2.2
|  2.3
|  |  3.3

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-05-28 : 11:10:01

Here is my partial answer
Rest is for your exercise


select reverse(stuff(tree,charindex('  ',tree),2,'--')) from
(
SELECT top 100 percent
--	*,
	reverse(REPLICATE('|  ', [Level]-1) + [Value]) as tree
FROM	@tblTree
ORDER BY [Sequence]
) as t

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2010-05-28 : 11:51:51

Nice reversing Madhi?

I think I can get a "`" is most cases by checking if next-sequence row is a lower level

That only leaves the middle pipe on 4.4, and first pipe on 3.3 to "hide", and middle pipe on 3.3 (1st one

) to change to "`"

I'll change the original data to make the Values unique - sorry about that

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2010-05-28 : 13:53:33

Sorry this is horrible, but I've been interviewing people all day and my brain's fried.


;WITH RT AS (
  SELECT TOP 1 [Sequence], [Level], [Value], REPLICATE(' ', [Level] - 1) + '`' AS tree
  FROM @tblTree
  ORDER BY [Sequence] DESC

  UNION ALL

  SELECT T.[Sequence], T.[Level], T.[Value],
    REPLACE(LEFT(RT.tree, T.[Level] - 1), '`', '|') +
      CASE WHEN T.[Level] = RT.[Level] THEN '|'
           WHEN T.[Level] > RT.[Level] THEN REPLICATE(' ', T.[Level] - RT.[Level] - 1) + '`'
           WHEN T.[Level] < RT.[Level] THEN REPLACE(SUBSTRING(RT.tree, T.[Level], 1),' ', '`')
      END
  FROM RT
  INNER JOIN @tblTree AS T
    ON T.[Sequence] = RT.[Sequence] - 1
)
SELECT
  STUFF(REPLACE(REPLACE(REPLACE(STUFF(RT.tree, 1, 1, ''), ' ', '   '), '`', '  `'), '|', '  |') + '--', 1, 2, '') + [Value]
FROM RT
ORDER BY [Sequence]
OPTION (MAXRECURSION 0)

Kristen
Test

22859 Posts

Posted - 2010-05-29 : 01:47:58

Arnold that is excellent! Thanks very much.

~~Two~~One data sets that it coughs on:


SELECT	[Sequence] = 1, [Level] = 1, [Value] = '1.1' UNION ALL
SELECT	2, 2, '2.1' UNION ALL
SELECT	3, 3, '3.1' UNION ALL
SELECT	4, 3, '3.2' UNION ALL
SELECT	5, 4, '4.1' UNION ALL
SELECT	6, 4, '4.2' UNION ALL
SELECT	7, 3, '3.3' UNION ALL
SELECT	8, 4, '4.4' UNION ALL
SELECT	9, 2, '2.2' UNION ALL
SELECT	10, 2, '2.3' 
UNION ALL SELECT 11, 3, '3.3'
UNION ALL SELECT 12, 4, '4.5'
UNION ALL SELECT 13, 5, '5.1'
UNION ALL SELECT 14, 1, '1.2'

and

SELECT	[Sequence] = 1, [Level] = 1, [Value] = '1.1' UNION ALL
SELECT	2, 2, '2.1' UNION ALL
SELECT	3, 3, '3.1' UNION ALL
SELECT	4, 3, '3.2' UNION ALL
SELECT	5, 4, '4.1' UNION ALL
SELECT	6, 4, '4.2' UNION ALL
SELECT	7, 3, '3.3' UNION ALL
SELECT	8, 4, '4.4' UNION ALL
SELECT	9, 2, '2.2' UNION ALL
SELECT	10, 2, '2.3' 
-- UNION ALL SELECT 11, 3, '3.3'
-- UNION ALL SELECT 12, 4, '4.5'
-- UNION ALL SELECT 13, 5, '5.1'
UNION ALL SELECT 1411, 1, '1.2'

I'll take a look later on and see if I can work up a fix - rain is forecast today

EDIT: Same issue, both data sets, once I got the Sequence Number correct - sorry about that!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources