January 17, 2013 at 2:14 am
Hello Friends,
Following is the DDL, Sample Data for the requirement I have :
--DDL
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ES_SubModuleMaster1](
[SModuleId] [bigint] IDENTITY(1,1) NOT NULL,
[ModuleId] [bigint] NOT NULL,
[SMName] [varchar](100) NOT NULL,
[SDate] [datetime] NULL,
[EDate] [datetime] NULL,
[Status] [varchar](10) NOT NULL,
[ToolTip] [varchar](500) NULL,
[ParentId] [bigint] NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
--Sample Data
Insert Into ES_SubModuleMaster1 Values(10,'Test','2013-01-15 00:00:00.000','2013-01-16 00:00:00.000','Start','vvvvv',0);
Insert Into ES_SubModuleMaster1 Values(10,'Test1','2013-01-15 00:00:00.000','2013-01-16 00:00:00.000','Start','fdsf',0);
Insert Into ES_SubModuleMaster1 Values(10,'Test_1','2013-01-15 00:00:00.000','2013-01-16 00:00:00.000','Start','zfdf',1);
Insert Into ES_SubModuleMaster1 Values(10,'Test_1_1','2013-01-15 00:00:00.000','2013-01-16 00:00:00.000','Start','dfds',3);
Insert Into ES_SubModuleMaster1 Values(10,'Test_1_2','2013-01-15 00:00:00.000','2013-01-16 00:00:00.000','Start','sfd',4);
Insert Into ES_SubModuleMaster1 Values(10,'Test1_1','2012-01-15 00:00:00.000','2013-01-16 00:00:00.000','Start','sfd',2);
Insert Into ES_SubModuleMaster1 Values(10,'Test_1_3','2013-01-15 00:00:00.000','2013-01-16 00:00:00.000','Start','view',1);
Insert Into ES_SubModuleMaster1 Values(10,'ZYX','2013-01-15 00:00:00.000','2013-01-23 00:00:00.000','Start','View',1);
Following is how I want the output to look like :
--Desired Output
110Test2013-01-15 00:00:00.0002013-01-16 00:00:00.000Startvvvvv0
310Test_12013-01-15 00:00:00.0002013-01-16 00:00:00.000Startzfdf1
410Test_1_12013-01-15 00:00:00.0002013-01-16 00:00:00.000Startdfds3
510Test_1_22013-01-15 00:00:00.0002013-01-16 00:00:00.000Startsfd4
710Test_1_32013-01-15 00:00:00.0002013-01-16 00:00:00.000Startview1
810ZYX2013-01-15 00:00:00.0002013-01-23 00:00:00.000StartView10
210Test12013-01-15 00:00:00.0002013-01-16 00:00:00.000Startfdsf0
610Test1_12012-01-15 00:00:00.0002013-01-16 00:00:00.000Startsfd 2
I want to display a Tree like structure where the first node starts from the row where ParentId = 0, then get the first sub node ie: the row where ParentId = 1.....the first sub node would be the row with SModuleId 3....then get all the sub nodes where ParentId would be 3.
Do the same for all sub nodes, before moving on to the next node where ParentId = 0 ie: the row where SModuleId = 2 and similarly get all the sub nodes and sub nodes of these subnodes.........just like a complete tree.
I hope the above explanation was helpful.
Can this be done without using a Cursor?....All suggestions are very very welcome....Looking forward to your replies.
January 17, 2013 at 3:08 am
Thanks Guys....
I got it to work using the following Recursive query :
;With RCTE
As
(
Select SModuleId,ModuleId,SMName,
Convert(char(11),SDate,106)as SDate, Convert(char(11), SDate, 103) as ddmmyyyySDate,
Convert(char(11),EDate,106) as EDate, Convert(char(11), EDate, 103) as ddmmyyyyEDate,
Status,ToolTip, ParentId, 0 As Level, SModuleId As Root, CAST(SModuleId AS varchar(MAX)) AS TreeOrder
from ES_SubModuleMaster1 Where SModuleId=Coalesce(NULL,SModuleId) AND ParentId = 0
Union ALL
Select b.SModuleId,b.ModuleId,b.SMName,
Convert(char(11),b.SDate,106)as SDate, Convert(char(11), b.SDate, 103) as ddmmyyyySDate,
Convert(char(11),b.EDate,106) as EDate, Convert(char(11), b.EDate, 103) as ddmmyyyyEDate,
b.Status, b.ToolTip, b.ParentId, a.Level + 1, a.Root As Root, a.TreeOrder+'/'+CAST(b.SmoduleId AS varchar(20)) AS TreeOrder
From RCTE As a JOIN ES_SubModuleMaster1 As b ON a.SModuleId = b.ParentId
)
Select * From RCTE Order By TreeOrder
Got the solution from here after some research...thought it might be useful to someone. 🙂
January 17, 2013 at 8:19 pm
This is a self-help forum. 😀
Help us to help you help yourself...
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
January 17, 2013 at 9:06 pm
dwain.c (1/17/2013)
This is a self-help forum. 😀Help us to help you help yourself...
The above solution still wasn't complete....it wasn't getting the sub trees randomly and not in any particular order....I had to add rownumber to the above solution to get that done too....not very pretty but gets what I want....better options are still welcome..........would love to get updated on this Dwain 🙂
January 17, 2013 at 9:29 pm
I don't think there's a need to use ROW_NUMBER(), unless something like this doesn't get it for you:
;WITH ModuleHieararchy AS (
SELECT [SModuleId], [ModuleId], [SMName]
,[SDate]
,[EDate]
,[Status]
,[ToolTip]
,[ParentId]
,n=1
,SM1=[SModuleId]
,SM2=[SModuleId]
FROM ES_SubModuleMaster1
WHERE [ParentId] = 0
UNION ALL
SELECT a.[SModuleId], a.[ModuleId], a.[SMName]
,a.[SDate]
,a.[EDate]
,a.[Status]
,a.[ToolTip]
,a.[ParentId]
,n+1
,SM1
,SM2=a.[SModuleId]
FROM ES_SubModuleMaster1 a
JOIN ModuleHieararchy b ON a.ParentID = b.sModuleID
)
SELECT [SModuleId], [ModuleId], [SMName]
,[SDate]
,[EDate]
,[Status]
,[ToolTip]
,[ParentId]
FROM ModuleHieararchy
ORDER BY SM1, SM2, n
Ignoring your CONVERTs on date of course.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
January 18, 2013 at 4:42 am
Hi Dwain,
I don't think your query would work if I change the data a little.
Try your query with this new data:
Insert Into ES_SubModuleMaster
Select 1,1,'Academic',2013-01-01 00:00:00.0002013-01-04 00:00:00.000Start0
Union ALL
Select 2,1,'Administration',2013-01-04 00:00:00.0002013-01-07 00:00:00.000Start0
Union ALL
Select 3,1,'Information',2013-01-07 00:00:00.0002013-01-08 00:00:00.000Start0
Union ALL
Select 4,1,'Personal Information',2013-01-10 00:00:00.0002013-01-12 00:00:00.000Start0
Union ALL
Select 5,1,'School Setup',2013-01-13 00:00:00.0002013-01-15 00:00:00.000Start0
Union ALL
Select 6,1,'Sign Out',2013-01-16 00:00:00.0002013-01-17 00:00:00.000Start0
Union ALL
Select 7,1,'Welcome Page',2013-01-18 00:00:00.0002013-01-21 00:00:00.000Start0
Union ALL
Select 8,1,'Content Management',2013-01-01 00:00:00.0002013-01-03 00:00:00.000Start1
Union ALL
Select 9,1,'Content',2013-01-01 00:00:00.0002013-01-03 00:00:00.000Start8
Union ALL
Select 11,1,'Shared File List',2013-01-08 00:00:00.0002013-01-14 00:00:00.000Start8
Union ALL
Select 12,1,'Shared File List For Faculty',2013-01-01 00:00:00.0002013-01-07 00:00:00.000Start8
Union ALL
Select 13,1,'Alumni List',2013-01-01 00:00:00.0002013-01-05 00:00:00.000Start2
Union ALL
Select 15,1,'Content Management For Parent',2013-01-01 00:00:00.0002013-01-13 00:00:00.000Start1
Union ALL
Select 16,1,'InContent',2013-01-15 00:00:00.0002013-01-17 00:00:00.000Start9
Any other options??....I'm still stuck with the Ordering :crying:
January 18, 2013 at 4:46 am
vinu512 (1/18/2013)
Hi Dwain,I don't think your query would work if I change the data a little.
Try your query with this new data:
Insert Into ES_SubModuleMaster
Select 1,1,'Academic',2013-01-01 00:00:00.0002013-01-04 00:00:00.000Start0
Union ALL
Select 2,1,'Administration',2013-01-04 00:00:00.0002013-01-07 00:00:00.000Start0
Union ALL
Select 3,1,'Information',2013-01-07 00:00:00.0002013-01-08 00:00:00.000Start0
Union ALL
Select 4,1,'Personal Information',2013-01-10 00:00:00.0002013-01-12 00:00:00.000Start0
Union ALL
Select 5,1,'School Setup',2013-01-13 00:00:00.0002013-01-15 00:00:00.000Start0
Union ALL
Select 6,1,'Sign Out',2013-01-16 00:00:00.0002013-01-17 00:00:00.000Start0
Union ALL
Select 7,1,'Welcome Page',2013-01-18 00:00:00.0002013-01-21 00:00:00.000Start0
Union ALL
Select 8,1,'Content Management',2013-01-01 00:00:00.0002013-01-03 00:00:00.000Start1
Union ALL
Select 9,1,'Content',2013-01-01 00:00:00.0002013-01-03 00:00:00.000Start8
Union ALL
Select 11,1,'Shared File List',2013-01-08 00:00:00.0002013-01-14 00:00:00.000Start8
Union ALL
Select 12,1,'Shared File List For Faculty',2013-01-01 00:00:00.0002013-01-07 00:00:00.000Start8
Union ALL
Select 13,1,'Alumni List',2013-01-01 00:00:00.0002013-01-05 00:00:00.000Start2
Union ALL
Select 15,1,'Content Management For Parent',2013-01-01 00:00:00.0002013-01-13 00:00:00.000Start1
Union ALL
Select 16,1,'InContent',2013-01-15 00:00:00.0002013-01-17 00:00:00.000Start9
Any other options??....I'm still stuck with the Ordering :crying:
It probably won't work if you added levels. See how I constructed SM1 and SM2? You'd need to do the same down to whatever level you think you've got and then use them in the ORDER BY.
Didn't say it was a complete solution. Just all I had time for and there to get you thinking about other ways to get your ordering right. A fully generalized solution to any level might require dynamic SQL.
Edit: Oh yes. And if you'd like me to look again, please post expected results as I'd rather not guess. 🙂
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
January 18, 2013 at 5:06 am
I modified your query a little....just to see the levels and the root columns and removed the dates as follows:
;WITH ModuleHieararchy AS (
SELECT SModuleId, ModuleId, SMName
,Status
,ParentId
,0 As Level, SModuleId As Root, CAST(SModuleId AS varchar(MAX)) AS TreeOrder
,n=1
,SM1=SModuleId
,SM2=SModuleId
FROM ES_SubModuleMaster
WHERE ParentId = 0
UNION ALL
SELECT a.SModuleId, a.ModuleId, a.SMName
,a.Status
,a.ParentId
,b.Level + 1, b.Root As Root, b.TreeOrder+'/'+CAST(a.SmoduleId AS varchar(20)) AS TreeOrder
,n+1
,SM1
,SM2=a.SModuleId
FROM ES_SubModuleMaster a
JOIN ModuleHieararchy b ON a.ParentID = b.sModuleID
)
SELECT SModuleId, ModuleId, SMName
,Status
,ParentId
,Level,Root,TreeOrder
FROM ModuleHieararchy
ORDER BY SM1, SM2, n
11Academic Start0011
81Content ManagementStart1111/8
91Content Start8211/8/9
111Shared File ListStart8211/8/11
121Shared File List For Faculty Start8211/8/12
161InContentStart9311/8/9/16
151Content Management For Parent Start1111/15
21AdministrationStart0022
131Alumni ListStart2122/13
31InformationStart0033
41Personal Information Start0044
51School SetupStart0055
61Sign OutStart0066
71Welcome PageStart0077
Following is the output that is required from the data that I posted in my last reply.....I hopw I can get it done before you for a change ;-)......thanks Dwain.
January 18, 2013 at 5:46 am
Not a fair race!
You:
- Removed the tool tip column from your INSERTs
- Your INSERTs were missing tons of commas and quotes
- You changed the IDENTITY_INSERT setting
- I think you changed the name of the table too.
- And most of all you tried to throw me off by forgetting to include IDs 10 and 14!!!!
But nonetheless, as I said you needed to add an additional SMn for each level of depth you need to process. Hence my suggestion that you try a dynamic SQL solution (which I know you're good at so I won't presume to show you how).
;WITH ModuleHieararchy AS (
SELECT [SModuleId], [ModuleId], [SMName]
,[SDate]
,[EDate]
,[Status]
--,[ToolTip]
,[ParentId]
,n=1
,SM1=[SModuleId]
,SM2=[SModuleId]
,SM3=[SModuleId]
,Tree=CAST([SModuleID] AS VARCHAR(8000))
FROM ES_SubModuleMaster1
WHERE [ParentId] = 0
UNION ALL
SELECT a.[SModuleId], a.[ModuleId], a.[SMName]
,a.[SDate]
,a.[EDate]
,a.[Status]
--,a.[ToolTip]
,a.[ParentId]
,n+1
,SM1
,SM2=CASE n WHEN 1 THEN a.[SModuleId] ELSE SM2 END
,SM3=CASE WHEN n >= 1 THEN a.[SModuleId] ELSE SM3 END
,Tree + '/' + CAST(a.[SModuleID] AS VARCHAR(8000))
FROM ES_SubModuleMaster1 a
JOIN ModuleHieararchy b ON a.ParentID = b.sModuleID
)
SELECT [SModuleId], [ModuleId], [SMName]
,[SDate]
,[EDate]
,[Status]
--,[ToolTip]
,[ParentId]
,n,sm1,sm2
,Tree
FROM ModuleHieararchy
ORDER BY SM1, SM2, SM3, n
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
January 18, 2013 at 5:57 am
Just prefix the id's in the TreeOrder column with an appropriate number of zeros's.
Test setup
Declare @table table(SModuleId Int, ModuleId Int, SMName Varchar(100), LinkOrder Int, ParentId int, Level int, Root int, TreeOrder Varchar(Max))
Insert Into @table
Select 1,1,'Academic',3,0,0,1,'1'
Union ALL
Select 8,1,'Content Management',2,1,1,1,'1/8'
Union ALL
Select 15,1,'Content Management For Parent',3,1,1,1,'1/15'
Union ALL
Select 9,1,'Content',1,8,2,1,'1/8/9'
Union ALL
Select 11,1,'Shared File List',2,8,2,1,'1/8/11'
Union ALL
Select 12,1,'Shared File List For Faculty',3,8,2,1,'1/8/12'
Union ALL
Select 16,1,'InContent',1,9,3,1,'1/8/9/16'
Union ALL
Select 2,1,'Administration',2,0,0,2,'2'
Union ALL
Select 13,1,'Alumni List',10,2,1,2,'2/13'
Union ALL
Select 3,1,'Information',4,0,0,3,'3'
Union ALL
Select 4,1,'Personal Information',5,0,0,4,'4'
Union ALL
Select 5,1,'School Setup',1,0,0,5,'5'
Union ALL
Select 6,1,'Sign Out',8,0,0,6,'6'
Union ALL
Select 7,1,'Welcome Page',7,0,0,7,'7'
select * from @table
Solution
;with ModuleTree as
(
select
t.SModuleId, t.ModuleId, t.SMName, t.SModuleId Root,
0 Level, right('00000000' + cast(SModuleId as varchar(max)), 9) TreeOrder
from
@table t
where
Parentid = 0
union all
select
t.SModuleId, t.ModuleId, t.SMName, t.Root,
mt.Level + 1, mt.TreeOrder + '/' + right('000000000' + cast(t.SModuleId as varchar(4)), 9)
from
@table t
join
ModuleTree mt on mt.SModuleId = t.ParentId
)
select
*
from
ModuleTree
order by
TreeOrder
January 18, 2013 at 6:10 am
Rats! Now why didn't I think of that?
+1 Peter
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
January 19, 2013 at 12:12 am
Thank You very much Peter. 🙂 Your solution is working very well....Let me do some more testing and will get back if something's missing.
Thank you Dwain for all your efforts. 🙂
January 21, 2013 at 4:54 am
Hello again guys.....how are you doing?
The query is working perfectly fine. I have implemented the query and tested it for a tree having sub trees till the eighth level.
Peter, i am still not very clear with the adding of the 0s to the TreeOrder string. Could you please tell me the logic behind doing so.....would love to know.
Dwain, I'm really sorry for the missing commas in the Insert statements........My mind was completely occupied with the query even when I was posting the sample data.....but u still beat me to it 🙂
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply