Recursive Query - Tough one - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Recursive Query - Tough one

Author

Topic

JohnBGood
Starting Member

48 Posts

Posted - 2009-10-03 : 20:30:36

I have this hierarical data.. the relationship between rows is defined by the parentID. For example, "Design Activities" is a parent to "Write Specs".

DECLARE @foo TABLE (
[ID] INT
,[AssID] INT
, TimeByDay Datetime
, [Task Name] NVARCHAR(255)
, [ParentId] INT
, [cost] MONEY
)

INSERT @foo
SELECT 1,null,null,'Design Activities', null, null
UNION SELECT 2,null,null, 'Write Specs', 1, null
UNION SELECT 3,10,1/1/2005,'Write UI Spec', 2, 5
UNION SELECT 3,10,1/2/2005 'Write UI Spec', 2, 5
UNION SELECT 3,11,1/3/2005, 'Write UI Spec', 2, 5
UNION SELECT 3,11,1/4/2005, 'Write UI Spec', 2, 5
UNION SELECT 4,12,1/1/2005, 'Write Data Spec', 2, 5
UNION SELECT 4,12,1/2/2005, 'Write Data Spec', 2, 5
UNION SELECT 4,12,1/3/2005, 'Write Data Spec', 2, 5
UNION SELECT 5,null,null, 'Build Prototypes', 1, null
UNION SELECT 6,13,11,1/6/2005 'Build Widget Prototype 1', 5, 15
UNION SELECT 6,13,11,1/7/2005 'Build Widget Prototype 1', 5, 15
UNION SELECT 6,13,11,1/8/2005 'Build Widget Prototype 1', 5, 15

I want to return the results at different levels of the hierarchy.

so if i put "Select <query> WHERE ID = 2", I would get the following results.

1/1/2005,10
1/2/2005,10
1/3/2005, 10
1/4/2005, 5

the parent task "write specs has an ID of 2 and has children Write UI Spec and Write Data Spec.

thanks in advance.. I'm really banging my head. I think a CTE is the way to approach this but cant get my head around it.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-10-04 : 05:53:06

can you show us how is your expected result looks like ?

KH
[spoiler]Time is always against us[/spoiler]

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-10-04 : 06:35:11

[code];WITH CTE(ID, [Task Name], depth, sort)
AS
(
SELECT ID, [Task Name], 0, CAST(ID AS VARBINARY(900))
FROM @foo
WHERE ID = 2
UNION ALL
SELECT f.ID, f.[Task Name], depth+1, CAST(sort + CAST(f.ID AS BINARY(4)) AS VARBINARY(900))
FROM @foo f join CTE c on f.ParentId=c.ID
)
SELECT REPLICATE('+--', depth) + ' ' + CAST(ID AS VARCHAR), [Task Name]
FROM CTE
ORDER BY sort[/code]

Hope can help...but advise to wait pros with confirmation...

JohnBGood
Starting Member

48 Posts

Posted - 2009-10-04 : 08:03:41

Waterduck... this looks very promising, but i get a syntax error when I execute.

Msg 205, Level 16, State 1, Line 10
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

I simply pasted your code below table declaration. What do you think?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-10-04 : 08:48:11

The last 3 inserts in your sample data give 7 values instead of 6 values.
There are some commas missing.
Datetime values need to be in single quotes.

No, you're never too old to Yak'n'Roll if you're too young to die.

JohnBGood
Starting Member

48 Posts

Posted - 2009-10-05 : 08:07:00

Thanks to All!!! Very nice solution! So much appreciated!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources