Prev: column to rows
Next: Single Row from Multiple Columns and Rows
From: Derek on 14 Apr 2010 16:20
using sql server 2005 sp3 32 bit

i have a table that looks like this (pseudo coded)

create table test (id int, parentid int, path varchar(200))

insert into test select 1, null, '/1/'
insert into test select 2, 1, '/1/2/'
insert into test select 3, 2, '/1/2/3/'
insert into test select 4, null, '/4/'
insert into test select 5, 4, '/4/5/'
insert into test select 6, 5, '/4/5/6/'

I need to produce a table that is flattened
like this

parentid, id
null 1
1 2
1 3
2 3
null 4
4 5
4 6
5 6

I can't get a CTE to work......... can anyone help?
From: Plamen Ratchev on 14 Apr 2010 17:05
Try this:

;WITH Hierarchy AS (
SELECT parentid, id, path
FROM test
WHERE parentid IS NULL
UNION ALL
SELECT H.id, T.id, T.path
FROM test AS T
JOIN Hierarchy AS H
ON T.path LIKE H.path + '%/')
SELECT parentid, id
FROM hierarchy
ORDER BY path;

--
Plamen Ratchev
http://www.SQLStudio.com
From: Derek on 14 Apr 2010 20:40
THanks!!! it's the LIKE that i was doing wrong... thanks again!

On Apr 14, 5:05 pm, Plamen Ratchev <Pla...(a)SQLStudio.com> wrote:
> Try this:
>
> ;WITH Hierarchy AS (
> SELECT parentid, id, path
> FROM test
> WHERE parentid IS NULL
> UNION ALL
> SELECT H.id, T.id, T.path
> FROM test AS T
> JOIN Hierarchy AS H
>    ON T.path LIKE H.path + '%/')
> SELECT parentid, id
> FROM hierarchy
> ORDER BY path;
>
> --
> Plamen Ratchevhttp://www.SQLStudio.com

 | 
Pages: 1
Prev: column to rows
Next: Single Row from Multiple Columns and Rows