• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 439
  • Last Modified:

sql order table with parentID

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
Jesper Christensen
Asked:
Jesper Christensen
  • 2
  • 2
1 Solution
 
momi_sabagCommented:
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
 
Jesper ChristensenProgrammerAuthor Commented:
I got this error:

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value '.' to data type int.
0
 
Jesper ChristensenProgrammerAuthor Commented:
There isen´t any solution to this?
0
 
momi_sabagCommented:
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

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now