parent child tree - 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

General SQL Server Forums

New to SQL Server Programming

parent child tree

Author

Topic

liran-e
Starting Member

8 Posts

Posted - 2010-12-27 : 20:26:53

Hi,
I've got a table that consists of two column: pid & sid
the table looks like this:
pid cid
1 2
1 3
3 4
3 5
5 6

I need to create another table that will contain parents & children all along the chain ... any suggestions ?

this should be the output:

pid cid
1 2
1 3
1 4
1 5
1 6
3 4
3 5
3 6
5 6

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-27 : 20:38:51

with cte as
(
select strtpid=pid, pid, cid, seq=1 from tbl
union all
select cte.strtpid, t.pid, t.cid from cte join tbl t on cte.cid=t.pid
)
select distinct pid=strtpid, cid
from cte

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

liran-e
Starting Member

8 Posts

Posted - 2010-12-27 : 20:43:11

my table name is r1

I've tried your query:
with cte as
(
select strtpid=pid, pid, cid, seq=1 from r1
union all
select cte.strtpid, t.pid, t.cid from cte join r1 t on cte.cid=t.pid
)
select distinct pid=strtpid, cid
from cte

but I'm getting this error msg:
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-27 : 20:53:09

Sorry - thought I would need seq but didn't
If this one doesn't work i'll try testing it

with cte as
(
select strtpid=pid, pid, cid from r1
union all
select cte.strtpid, t.pid, t.cid from cte join r1 t on cte.cid=t.pid
)
select distinct pid=strtpid, cid
from cte

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

liran-e
Starting Member

8 Posts

Posted - 2010-12-27 : 20:57:14

perfect :)
thank you very much

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources