Concat based on parent id

Say I have this
id	name	parent
1	cat 1	0
2	cat 2	1
3	cat 3	2
4	cat 4	3
5	cat 5	0
6	cat 6	5
7	cat 7	6

Open in new window

I want to select all rows (or rather store these ID's in each row of the table), but for each row concat the ID's of the parent hierarchy.
So say for cat 3 I would end up with
cat 3     1,2,3

...where 1, 2 and 3 are the ID's of the row itself, the ID of its parent and the ID of the parent. There is never more than 3 levels and the order doesn't matter.
LVL 58
Who is Participating?
Ray PaseurConnect With a Mentor Commented:
Gary: I am not sure this helps, but I've seen a similar/different question recently.  Please have a look:
Dave BaldwinFixer of ProblemsCommented:
I would suggest that you add a fourth field to store that in so you don't mess up your ability to trace thru your hierarchy.
GaryAuthor Commented:
Maybe but that is array based, I'm looking at an sql statement I can run one time on the database.
If nobody else comes along I'll look through it again, bit too much to digest right now.
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

You could use Common table Expressions (CTE). Here is a link to the example
GaryAuthor Commented:
What has MSSQL got to do with it?
Is nt it in SQL you are trying to achieve this?
GaryAuthor Commented:
Yes, but what you can do in MSSQL is not the same as what you an do in MySQL
GaryAuthor Commented:
Ended up just running a couple of queries to build it as it is only a one off exercise, but the linked to Q has all the logic for doing it.
Ray PaseurCommented:
Thanks, Gary!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.