SQL Server 2008 Common Table Expression

Question

5.00/5 (1 vote)

See more:

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.

SQL

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 29-Nov-11 23:23pm

Kevin Derrick Murphy

Add a Solution

2 solutions

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.

Posted 2-Dec-11 3:01am

Kevin Derrick Murphy

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 ;)

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Amir Mahfoozi · Accepted Answer · 2011-11-30T01:44:00

Solution 1

Hi Kevin,

Try this one :

SQL

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.

Posted 30-Nov-11 1:44am

Amir Mahfoozi

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