January 5, 2012 at 3:16 am
I have a table named as Modules
CREATE TABLE #Modules
(
[ModuleId] [int] ,
[ModuleName] [varchar](50) NOT NULL,
[ParentId] [int] NULL
Insert into #Modules (1, 'Master', null)
Insert into #Modules (2, 'List', 1)
Insert into #Modules (3, 'EmployeeList', 2)
Insert into #Modules (4, 'UsersList', 2)
Insert into #Modules (5, 'Groups', 1)
Insert into #Modules (6, 'EmployeeGroup', 5)
Insert into #Modules (7, 'UsersGroup', 5)
)
Now what I want is select module name with lowest parent name
like
"Master->EmployeeList"
"Master->UsersList"
Master->'EmployeeGroup'
Master->'UsersGroup'
I need above mentioned output
January 5, 2012 at 3:38 am
There are example recursive CTE's all over the web.
A near identical example is on the Microsoft website. This smells like homework
January 5, 2012 at 4:14 am
CREATE TABLE #Modules ([ModuleId] INT, [ModuleName] VARCHAR(50) NOT NULL, [ParentId] INT NULL)
INSERT INTO #Modules
SELECT 1, 'Master', NULL
UNION ALL SELECT 2, 'List', 1
UNION ALL SELECT 3, 'EmployeeList', 2
UNION ALL SELECT 4, 'UsersList', 2
UNION ALL SELECT 5, 'Groups', 1
UNION ALL SELECT 6, 'EmployeeGroup', 5
UNION ALL SELECT 7, 'UsersGroup', 5;
WITH CTE AS (SELECT 1 AS [Level], [ModuleId],
CONVERT(VARCHAR(MAX), [ModuleName]) AS [ModuleName], [ParentId]
FROM #Modules
WHERE [ParentId] IS NULL
UNION ALL SELECT cycle.[Level] + 1, base.[ModuleId],
cycle.[ModuleName] + '-->' + base.[ModuleName], base.[ParentId]
FROM #Modules base
INNER JOIN CTE cycle ON cycle.[ModuleId] = base.[ParentId])
SELECT [ModuleId], [ModuleName]
FROM CTE
January 5, 2012 at 4:19 am
I tried from my side but it will give me following output
WITH
cteMenu AS
(
SELECT ModuleId, ParentId, Caption, Level = 1, HierarchicalPath = CAST(CAST(Caption AS VARCHAR(50)) AS VARCHAR(4000))
FROM dbo.modules
WHERE parentid IS NULL
UNION ALL
SELECT e.ModuleId, e.ParentId, e.Caption,Level = d.Level + 1,
HierarchicalPath = CAST(d.HierarchicalPath + '>>'.Caption AS VARCHAR(50)) AS VARCHAR(4000))
FROM dbo.sys_modules e
INNER JOIN cteMenu d ON e.ParentId = d.moduleid
)
SELECT ModuleId,
ParentId,
Name = SPACE((Level-1)*4) + Caption,
Level,
HierarchicalPath
FROM cteMenu
ORDER BY HierarchicalPath
I will give Me out put like 'Parent>>Child>>Grand Child'
i need 'Parent>>Grand Child'
Vijay
January 5, 2012 at 4:45 am
vijay.s (1/5/2012)
@CadavreYour query is also returning the same result as mentioned above.
I just want to lowest parent (nth parent) and highest child.
Oh, misread the requirements.
CREATE TABLE #Modules ([ModuleId] INT, [ModuleName] VARCHAR(50) NOT NULL, [ParentId] INT NULL)
INSERT INTO #Modules
SELECT 1, 'Master', NULL
UNION ALL SELECT 2, 'List', 1
UNION ALL SELECT 3, 'EmployeeList', 2
UNION ALL SELECT 4, 'UsersList', 2
UNION ALL SELECT 5, 'Groups', 1
UNION ALL SELECT 6, 'EmployeeGroup', 5
UNION ALL SELECT 7, 'UsersGroup', 5;
WITH CTE AS (SELECT 1 AS [Level], [ModuleId],
[ModuleName] AS [GrandParent], [ModuleName], [ParentId]
FROM #Modules
WHERE [ParentId] IS NULL
UNION ALL SELECT cycle.[Level] + 1, base.[ModuleId],
cycle.[GrandParent], base.[ModuleName], base.[ParentId]
FROM #Modules base
INNER JOIN CTE cycle ON cycle.[ModuleId] = base.[ParentId])
SELECT [ModuleId], COALESCE(NULLIF([GrandParent],[ModuleName])+'-->'+[ModuleName],[ModuleName]) AS [ModuleName]
FROM CTE
--EDIT--
That might not be what you're after either. Do you mean that you only want to display the root and any leaf that has no children?
January 5, 2012 at 4:56 am
If you did mean that all you want is the "root" and the last leaf, try this instead: -
BEGIN TRAN
CREATE TABLE #Modules ([ModuleId] INT, [ModuleName] VARCHAR(50) NOT NULL, [ParentId] INT NULL)
INSERT INTO #Modules
SELECT 1, 'Master', NULL
UNION ALL SELECT 2, 'List', 1
UNION ALL SELECT 3, 'EmployeeList', 2
UNION ALL SELECT 4, 'UsersList', 2
UNION ALL SELECT 5, 'Groups', 1
UNION ALL SELECT 6, 'EmployeeGroup', 5
UNION ALL SELECT 7, 'UsersGroup', 5;
WITH CTE AS (SELECT 1 AS [Level], [ModuleId],
[ModuleName] AS [GrandParent], [ModuleName], [ParentId]
FROM #Modules
WHERE [ParentId] IS NULL
UNION ALL SELECT cycle.[Level] + 1, base.[ModuleId],
cycle.[GrandParent], base.[ModuleName], base.[ParentId]
FROM #Modules base
INNER JOIN CTE cycle ON cycle.[ModuleId] = base.[ParentId])
SELECT nodes.[ModuleId],
COALESCE(NULLIF(nodes.[GrandParent],nodes.[ModuleName])+'-->'+nodes.[ModuleName],nodes.[ModuleName]) AS [ModuleName]
FROM CTE nodes
--Get child ID
OUTER APPLY (SELECT [ParentId], [ModuleId] AS childid
FROM CTE child
WHERE nodes.[ModuleId] = child.[ParentId]) children
WHERE childid IS NULL
ROLLBACK
January 5, 2012 at 5:18 am
Yeh..
You are right other wise where ParentId is null condition is enough for me as i have logic in my front end that call only child node. I need this query only show the concatenate caption to user.
January 5, 2012 at 12:49 pm
CELKO (1/5/2012)
...Buy a copy of HIKING IN SETS for more details...
My cat bought this book and got completely lost on the first example - with a catnav.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
January 5, 2012 at 1:45 pm
CELKO
Buy a copy of HIKING IN SETS for more details.
Right after I finish THINKING IN THE WOODS.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy