August 21, 2008 at 10:05 pm
Hi All
Assume I have a organisation chart table as followed
IF (OBJECT_ID ('dbo.SampleOrg', 'U') IS NOT NULL)
DROP TABLE dbo.SampleOrg
GO
CREATE TABLE dbo.SampleOrg
(
LevelIDINT NOT NULL PRIMARY KEY,
PositionNVARCHAR(50) NOT NULL,
ReportingLevelIDINT REFERENCES dbo.SampleOrg (LevelID)
)
GO
-- Insert some sample data into the table based on the structure
-- shown above
INSERT INTO dbo.SampleOrg SELECT 1, 'Chief Executive Officer', NULL
INSERT INTO dbo.SampleOrg SELECT 2, 'Senior Director - Development', 1
INSERT INTO dbo.SampleOrg SELECT 3, 'Senior Director - Finance', 1
INSERT INTO dbo.SampleOrg SELECT 4, 'Senior Director - Human Resources', 1
INSERT INTO dbo.SampleOrg SELECT 5, 'Product Development Manager', 2
INSERT INTO dbo.SampleOrg SELECT 6, 'Project Lead', 5
INSERT INTO dbo.SampleOrg SELECT 7, 'QA Lead', 5
INSERT INTO dbo.SampleOrg SELECT 8, 'Documentation Lead', 5
INSERT INTO dbo.SampleOrg SELECT 9, 'Developers', 6
INSERT INTO dbo.SampleOrg SELECT 10, 'Testers', 7
INSERT INTO dbo.SampleOrg SELECT 11, 'Writers', 8
INSERT INTO dbo.SampleOrg SELECT 12, 'Accountants', 3
INSERT INTO dbo.SampleOrg SELECT 13, 'HR Professionals', 4
GO
I would like to print out in all the position that reports to "Product Development" like this
"Project Lead;QA Lead;Documentation Lead"
How can I do this using Recursive CTE?
Thanks All
August 22, 2008 at 12:39 am
Hi Catcha,
Please find the code below:
Declare @position Varchar(255)
Set @position ='Project Lead'
;WITH H_LIST AS
(
SELECT S.LevelID, S.Position , S.ReportingLevelID , 0 AS H_LEVEL
FROM dbo.SampleOrg S
WHERE S.Position = @position
UNION ALL
SELECT S1.LevelID, S1.Position , S1.ReportingLevelID , H_LEVEL + 1
FROM dbo.SampleOrg S1
INNER JOIN H_LIST L ON S1.ReportingLevelID=L.LevelID
)
SELECT * FROM H_LIST
ORDER BY 1
Please change the Position variable with value from where you want to start finding the hierarchy.
Thanks,
Amit Khanna
August 22, 2008 at 1:08 am
Why do you want to implement recursion as you are searching only for only 1 level
As you mention :
all the position that reports to "Product Development"
e.g "Project Lead;QA Lead;Documentation Lead" "
then you can do it only in 1 query. where ReportingLevelID = 5 / levelPOsition = "Product Product Development"
August 25, 2008 at 1:14 am
Hi Catcha,
Do u got what you was looking for ??
Thanks,
Amit Khanna
August 25, 2008 at 6:29 pm
Hi All
The query return in a table however I would like to return in 1 single row. eg "Project Lead;Developers"
Thanks
August 26, 2008 at 12:31 am
Try this one.......
Declare @position Varchar(255)
Set @position ='Product Development Manager'
;WITH H_LIST AS
(
SELECT S.LevelID, S.Position , S.ReportingLevelID , 0 AS H_LEVEL , S.Position AS STRING
FROM dbo.SampleOrg S
WHERE S.Position = @position
UNION ALL
SELECT S1.LevelID, S1.Position , S1.ReportingLevelID , H_LEVEL + 1 , L.STRING + ';' + S1.Position
FROM dbo.SampleOrg S1
INNER JOIN H_LIST L ON S1.ReportingLevelID=L.LevelID
)
SELECT * FROM H_LIST
ORDER BY 1
August 26, 2008 at 12:40 am
This is what I get when trying to run the query
Msg 240, Level 16, State 1, Line 6
Types don't match between the anchor and the recursive part in column "String" of recursive query "H_LIST".
August 26, 2008 at 1:01 am
Change the definition of Position Column to
Position VARCHAR(8000) NOT NULL
This will work.
Thanks,
Amit Khanna
August 26, 2008 at 1:31 am
if you want to query only 1 level then what about this
select reportinglevelid ,
STUFF ((select (',' + Position)
from SampleOrg where reportinglevelid = 5
group by Position
for xml path ('')),1,1,'')
FROM SampleOrg where reportinglevelid = 5
GROUP BY reportinglevelid.
I got this from a jeff modem article.
"Keep Trying"
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply