Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

sql order table with parentID

Posted on 2008-10-21
4
Medium Priority
?
434 Views
Last Modified: 2012-06-21
Hi.

Please look at this question:
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_23827855.html?cid=748#a22762575

It has been closed, so I cant ass comments to it.

I have a table:
ID   |   Name   |   ParentID
1    |  product1 |  NULL
2    |  product2 |  1
3    |  product3 |  2
4    |  product4 |  3
5    |  product5 |  4
6    |  product6 |  5

And I want to make a sql query who orders the products like etc:
Parent:
First child
Grandchildren
great grandchildren
second child
etc.

This SQL extracts all posts from the table:
;with product_hierarchy as
(select id,parentid,1 lev from products where parentid is null
union all
select p.id,p.parentid,ph.lev+1 from products p
join product_hierarchy ph
on ph.id = p.parentid
)
select * from product_hierarchy
0
Comment
Question by:bongii
  • 2
  • 2
4 Comments
 
LVL 37

Expert Comment

by:momi_sabag
ID: 22767730
try this

with product_hierarchy as
(select id,parentid,1 lev , id as path
from products where parentid is null
union all
select p.id,p.parentid,ph.lev+1 , ph.path+'.'+p.id as path
from products p
join product_hierarchy ph
on ph.id = p.parentid
)
select * from product_hierarchy
order by path
0
 

Author Comment

by:bongii
ID: 22768422
I got this error:

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value '.' to data type int.
0
 

Author Comment

by:bongii
ID: 22784897
There isen´t any solution to this?
0
 
LVL 37

Accepted Solution

by:
momi_sabag earned 2000 total points
ID: 22784914
try this

with product_hierarchy as
(select id,parentid,1 lev , cast(id as varchar(max)) as path
from products where parentid is null
union all
select p.id,p.parentid,ph.lev+1 , ph.path+'.'+ cast(p.id as varchar(max)) as path
from products p
join product_hierarchy ph
on ph.id = p.parentid
)
select * from product_hierarchy
order by path
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Loops Section Overview

876 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question