Solved

sql order table with parentID

Posted on 2008-10-21
4
410 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
Comment Utility
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
Comment Utility
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
Comment Utility
There isen´t any solution to this?
0
 
LVL 37

Accepted Solution

by:
momi_sabag earned 500 total points
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

772 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now