Click here to Skip to main content
12,065,756 members (32,810 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: SQL Server table
I have a SQL Server 2008 table with three columns, ID, ProvinceID and Name
ID and ProvinceID are both integers. ID is an identity for each item and ProvinceID is either 0 or an ID entry to indicate its parent. A sample of the table count as entered is below:

ID ProvinceID Name
1 9 Munster
2 1 Cork
3 9 Leinster
4 3 Dublin
5 1 Waterford
6 3 Wexford
7 3 Louth
8 1 Tipperary
9 0 Ireland

I wish to open the table so that it lists the records in parent - child - grandchild, for example:
9 0 Ireland
3 9 Leinster
4 3 Dublin
7 3 Louth
6 3 Wexford
1 9 Munster
2 1 Cork
8 1 Tipperary
5 1 Waterford

I have been told that a Common Table Expression should be able to do what I am looking for but have been unable to get it to work. The last entry in the table (Ireland) was added as the sample on the MSDN site was uaing a NULL for the equivalent field.

I have the following script in SQL Server Management Studio to try achieve this. It does not indicate any issues at all.

DECLARE @oldCountryPath as nvarchar(10) = 'x';
WITH search_graph (ID,ProvinceID,Name, ProvDepth, CountryPath )
AS (
  SELECT [ID],[ProvinceID],[Name], 0 AS ProvDepth, 'x' as CountryPath
  FROM [Counties]
  WHERE [ID] = 1
  UNION ALL
  SELECT e.ID, e.ProvinceID, e.Name, d.ProvDepth + 1 AS ProvDepth,
	d.CountryPath + CONVERT(nvarchar, 'x' )as CountryPath
  FROM [Counties] AS e
	INNER JOIN search_graph AS d
	ON e.ProvinceID = d.ID
	WHERE e.ProvinceID = d.ID
)
SELECT [ID],[ProvinceID],[Name], ProvDepth, CountryPath
FROM search_graph
ORDER BY [ID],[ProvDepth]

However, when I execute it I get this error:

Types don't match between the anchor and the recursive part in column "CountryPath" of recursive query "search_graph".

Can anyone tell me what I am missing or am doing wrong?

Thanks in advance.
Posted 30-Nov-11 0:23am
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

Hi Kevin,

Try this one :

WITH search_graph (ID,ProvinceID,Name, ProvDepth, CountryPath )
AS (
  SELECT [ID],[ProvinceID],[Name], 0 AS ProvDepth, cast(id as varchar(max)) as CountryPath
  FROM [Counties]
  WHERE provinceid = 0
  UNION ALL
  SELECT e.ID, e.ProvinceID, e.Name, d.ProvDepth + 1 AS ProvDepth,
    cast(d.CountryPath + cast(e.id as varchar) as varchar(max))as CountryPath
  FROM [Counties] AS e
    INNER JOIN search_graph AS d
    ON e.ProvinceID = d.ID
    WHERE e.ProvinceID = d.ID
)
SELECT [ID],[ProvinceID],[Name], ProvDepth, CountryPath
FROM search_graph
ORDER BY CountryPath


Hope It Helps.
  Permalink  
Comments
Kevin Derrick Murphy 30-Nov-11 8:35am
   
Amir, thanks that went a long way and resolved something that was driving me up the wall trying to figure out.

The only thing that it does not do is sort the 'grandchildren'. For example, I end up with Munster --> Cork, Waterford, Tipperary instead of Munster --> Cork, Tipperary, Waterford. This looks to be due to the fact that an ORDER BY cannot be used within the CTE. Adding the Name or ID field at the end of the complete query makes no difference.

Am I correct in thinking there is no way around this with the current setup?

Thanks again.
Amir Mahfoozi 30-Nov-11 9:31am
   
As I experienced there is nothing impossible in SQL ! so try to make a criteria for grand children in one of the CTEs and sort entire result by that criteria.
And remember that sometimes it is possible to do more actions on the result set in your application side ;)
Good Luck
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

Thanks Amir.

I found a way of doing it - combining the name in to a string. It works but is only practical for a handful of children. Givs me something to work with though.
  Permalink  
Comments
Amir Mahfoozi 2-Dec-11 13:34pm
   
I promise you that if you think more you will find multiple new ways to do the job but after a while you will be very experienced in encountering similar issues. So don't hurry and think and think more ;)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web02 | 2.8.160207.1 | Last Updated 2 Dec 2011
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100