super simple hierachy - 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 2005 Forums

Transact-SQL (2005)

super simple hierachy

Author

Topic

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-09-09 : 22:33:55

[code]DECLARE @tempfun TABLE(col1 VARCHAR(20),col2 VARCHAR(20))
INSERT INTO @tempfun
SELECT
"Page1", NULL UNION SELECT
"Pagex", "Page1" UNION SELECT
"Pagey", "Page1" UNION SELECT
"Pagez", "Page1" UNION SELECT
"Pagea", "Page1" UNION SELECT
"Pagew", "Page1" UNION SELECT
"Page2", NULL UNION SELECT
"Pageq", "Page2" UNION SELECT
"Pager", "Page2" UNION SELECT
"Pagee", "Page2" UNION SELECT
"Pagek", "Page2"

How can i hierachy it?
-Page1
--Pagex
--Pagey
--Pagez
--Pagea
--Pagew
-Page2
--Pageq
--Pager
--Pagee
--Pagek

the following will not work
WITH CTE(Page)
AS
(
--anchor
SELECT Page
FROM Webpage
WHERE Page_Parent IS NULL
UNION ALL
--recursive
SELECT w.Page
FROM Webpage w join cte c on w.page_parent=c.page
WHERE Page_Parent IS NOT NULL
)
SELECT Page FROM CTE
[/code]

Hope can help...but advise to wait pros with confirmation...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-09-09 : 22:48:51

isn't this link http://blogs.conchango.com/christianwade/archive/2004/11/09/234.aspx that you posted before describe the method you required here ?

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

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-09-09 : 22:59:00

ya....but not sure why i follow almost totally duplicate but cant works

Hope can help...but advise to wait pros with confirmation...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-09-09 : 23:13:32

one simple way of doing it ...


DECLARE @WebPage TABLE
(
	Page VARCHAR(20),
	Page_Parent VARCHAR(20)
)
INSERT INTO @WebPage
SELECT 	'Page1', NULL 	UNION ALL
SELECT	'Pagex', 'Page1' UNION ALL
SELECT	'Pagey', 'Page1' UNION ALL
SELECT	'Pagez', 'Page1' UNION ALL
SELECT	'Pagea', 'Page1' UNION ALL
SELECT	'Pagew', 'Page1' UNION ALL
SELECT	'Page2', NULL 	UNION ALL
SELECT	'Pageq', 'Page2' UNION ALL
SELECT	'Pager', 'Page2' UNION ALL
SELECT	'Pagee', 'Page2' UNION ALL
SELECT	'Pagek', 'Page2' union all
SELECT	'Pageaa', 'Pagea'

; WITH 
WebPage 
as
(
	Select 	Page, Page_Parent, row_id = row_number() over (order by Page)
	from	@WebPage
),
cte(Page, Sort)
AS
(
	--anchor
	SELECT	Page, Sort = convert(varchar(max), right('00000' + convert(varchar(10), row_id), 5))
	FROM	WebPage
	WHERE	Page_Parent IS NULL
	UNION ALL

	--recursive

	SELECT	w.Page, Sort = convert(varchar(max), c.Sort + right('00000' + convert(varchar(10), w.row_id), 5))
	FROM	WebPage w 
		INNER JOIN cte c on w.Page_Parent=c.Page
	WHERE	Page_Parent IS NOT NULL
)
SELECT 	Page, Sort
FROM 	cte
order by Sort

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

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-09-09 : 23:15:49

[code]SELECT REPLICATE('-', odr)+ col1
FROM (
SELECT row_number() over (order by col1) num, col1, 1 odr
FROM @tempfun
WHERE col2 IS NULL
UNION
SELECT dense_rank() over (order by col2) num, col1, 2 odr
FROM @tempfun
WHERE col2 IS NOT NULL)t
ORDER BY num, odr[/code]
sorry been busy about other thing also