Return a hierarchy: CTE or not

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 2008 Forums

Transact-SQL (2008)

Return a hierarchy: CTE or not

Author

Topic

skillilea
Starting Member

15 Posts

Posted - 2012-02-03 : 16:46:42

I am trying to create a hierarchy structure. I do have some flexiblity on the storage but, here is what it is today.

tblMentor
mentorID
mentorGroupName
mentorGroupOrder

tblEntity
entityID
mentorID
entityParent
entityName
entityOrder

Results:

mentorG1
entity 1
entity 13
entity 14
entity 2
e 15
e 16

Every entity will be in one group

Should I use a CTE or Lineage with depth.

Any help...

Note: There is no TOP entity. The results come back with all by mentorGroup.

Thanks tons!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-03 : 17:47:01

how are data stored in tables?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

skillilea
Starting Member

15 Posts

Posted - 2012-02-03 : 17:55:18

Mentors
1 Biz Systems 1
2 Quality 2
3 Finance 3
4 Customer 4

Entity
1 1 NULL IT 1
2 2 NULL FMEA 1
3 2 NULL PPAP 2
4 2 3 PPAP1 1
5 2 4 PPAP1:1 1
6 2 3 PPAP2 2

Note: I can change the data structure if needed.
I will have a tree of data buy I need to have the order right by Node.
Everything has a mentorGroup ID

Thanks for the help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-03 : 18:01:10

i think seeing data you can go for the recursive cte approach

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

skillilea
Starting Member

15 Posts

Posted - 2012-02-03 : 18:09:10

I've never done one...any help or samples??

tnx

skillilea
Starting Member

15 Posts

Posted - 2012-02-03 : 18:40:23

This is where I got to...

Gives me this error
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

DECLARE @mentor as TABLE (
id int IDENTITY(1,1)
,parentID int
,mentorID int
,mentorOrder int
,mentorName varchar(100)
)

INSERT INTO @mentor
SELECT NULL, 0, 0, 'Parent'
UNION
SELECT 1, mg.mentorGroupID, mg.mentorGroupOrder, mg.mentorGroupName
FROM InnoCommon.dbo.tblMentorGroup mg
WHERE mg.mentorGroupType = 1

WITH hierarchy(parentID, ID, level, mentorID, mentorOrder, mentorName) AS
(
SELECT parentID, ID, 0, mentorID, mentorOrder, mentorName
FROM @mentor
WHERE parentID IS NULL
UNION ALL
SELECT m.parentID, m.ID, level+1, m.mentorID, m.mentorOrder, m.mentorName
FROM @mentor m
INNER JOIN hierarchy h ON m.parentID = h.ID
)
SELECT ID, parentID, mentorName, level
FROM hierarchy

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-02-03 : 19:42:05

That error message is exactly what it says - you need to end the previous statement just before the "WITH" with a semi-colon. This is required because the WITH keyword is used in different contexts, and the semi-colon makes it clear that it is being used as the start of a CTE.


...
WHERE mg.mentorGroupType = 1; --<<<<--- THIS SEMI-COLON

WITH hierarchy(parentID, ID, level, mentorID, mentorOrder, mentorName) AS
...

To avoid this problem, many people automatically place a semicolon just before the WITH keyword when they are writing a CTE.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources