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 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply