August 12, 2011 at 3:11 pm
I am trying to write a recursive query to return a tree structure. I can return the top most node but not the entirre tree.
The result should look like:
Apparatus
Autoclave
Red - Autoclave
Blue - Autoclave
Thermometer
Maximum Reading
Glass
Farenheit
Celsius
My query is:
With CatList (Child, Parent,IDNo, ParentIDNo, Clearance) as
(Select ChildCategory, Category, IDNo, ParentIDNo,Clearance
From Category
where parentidno is null
Union All
Select Cat.ChildCategory, Cat.Category, Cat.IDNo, Cat.ParentIDNo ,Cat.Clearance
From Category Cat
Inner join CatList List
On cat.IDNo = List.ParentIDNo
)
select * from CatList
To create a table and populate it with data:
IF EXISTS (SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = USER
AND TABLE_NAME = 'Category')
DROP TABLE Category
go
CREATE TABLE Category
([IDNo] [bigint] NOT NULL,
[Category] [varchar](100) NULL,
[ChildCategory] [varchar](150) NULL,
[ParentIDNo] [bigint] NULL,
[WSWebProductNameIDNo] [bigint] NULL,
[WSWebPriceTableCellsItemNoIDNo] [bigint] NULL,
[ItemNo] [varchar](max) NULL,
[TopMostLevel] [bit] NULL,
[Clearance] [bit] NULL)
go
INSERT INTO Category VALUES (1, 'Apparatus', NULL,NULL,NULL,Null,NULL,1,NULL )
INSERT INTO Category VALUES (2, 'Apparatus','Autoclaves',1,NULL,NULL,NULL,Null,NULL)
INSERT INTO Category VALUES (3, 'Autoclaves','Red - Autoclave',2,100,NULL,NULL,Null,NULL)
INSERT INTO Category VALUES (4, 'Autoclaves','Blue - Autoclave',2,105,NULL,NULL,Null,NULL)
INSERT INTO Category VALUES (5, 'Thermometers', NULL,NULL,NULL,Null,NULL,1,NULL )
INSERT INTO Category VALUES (6, 'Thermometers','Maximum Registering',5,187,NULL,NULL,Null,NULL)
INSERT INTO Category VALUES (7, 'Thermometers','Glass',5,NULL,NULL,NULL,Null,NULL)
INSERT INTO Category VALUES (8, 'Glass','Farenheit',7,201,NULL,NULL,Null,NULL)
INSERT INTO Category VALUES (9, 'Glass','Celsius',7,209,NULL,NULL,Null,NULL)
go
Thank you for your assitance,
pat
August 12, 2011 at 3:31 pm
You have your join criteria backwards.
Try this.
With CatList (Child, Parent,IDNo, ParentIDNo, Clearance) as
(Select ChildCategory, Category, IDNo, ParentIDNo,Clearance
From Category
where parentidno is null
Union All
Select Cat.ChildCategory, Cat.Category, Cat.IDNo, Cat.ParentIDNo ,Cat.Clearance
From Category Cat
Inner join CatList List
On cat.ParentIDNo = List.IDNO
)
select * from CatList
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 12, 2011 at 4:16 pm
Thanks. It was backwards. I get all the records now.
However they are not sorted in a recursive manner. The data I posted yields misleading results as it was all in order when it was loaded.
Here is the data out of order.
I am not sure a simple Order by will fix the problem.
IF EXISTS (SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = USER
AND TABLE_NAME = 'Category')
DROP TABLE Category
go
CREATE TABLE Category
([IDNo] [bigint] NOT NULL,
[Category] [varchar](100) NULL,
[ChildCategory] [varchar](150) NULL,
[ParentIDNo] [bigint] NULL,
[WSWebProductNameIDNo] [bigint] NULL,
[WSWebPriceTableCellsItemNoIDNo] [bigint] NULL,
[ItemNo] [varchar](max) NULL,
[TopMostLevel] [bit] NULL,
[Clearance] [bit] NULL)
go
INSERT INTO Category VALUES (1, 'Apparatus', NULL,NULL,NULL,Null,NULL,1,NULL )
INSERT INTO Category VALUES (6, 'Apparatus','Autoclaves',1,NULL,NULL,NULL,Null,NULL)
INSERT INTO Category VALUES (3, 'Autoclaves','Red - Autoclave',6,100,NULL,NULL,Null,NULL)
INSERT INTO Category VALUES (4, 'Autoclaves','Blue - Autoclave',6,105,NULL,NULL,Null,NULL)
INSERT INTO Category VALUES (2, 'Thermometers', NULL,NULL,NULL,Null,NULL,1,NULL )
INSERT INTO Category VALUES (6, 'Thermometers','Maximum Registering',2,187,NULL,NULL,Null,NULL)
INSERT INTO Category VALUES (7, 'Thermometers','Glass',2,NULL,NULL,NULL,Null,NULL)
INSERT INTO Category VALUES (8, 'Glass','Farenheit',7,201,NULL,NULL,Null,NULL)
INSERT INTO Category VALUES (9, 'Glass','Celsius',7,209,NULL,NULL,Null,NULL)
go
thanks,
pat
August 12, 2011 at 4:18 pm
I'm not sure what you mean.
Post a sample of how you expect to see it displayed
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 12, 2011 at 4:21 pm
Wow. that was quick.
The order was in my original post.
Here it is. For some reason the indents are not show. I do not need it returned with indents. But it would have made it easier to read.
Apparatus
Autoclave
Red - Autoclave
Blue - Autoclave
Thermometer
Maximum Reading
Glass
Farenheit
Celsius
Please see jpg
August 12, 2011 at 4:25 pm
Same query i provided will yield these results.
Are you looking for indentation as well? It does not matter what order the data appears in the table - so long as the data references correct parents.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 12, 2011 at 4:46 pm
Jason,
No thank you. Indentation is not necessary.
I copied your query just now into SSMS. The results are shown in the jpg. Do you get something different?
August 12, 2011 at 5:06 pm
This will work better for you then.
With CatList (Child, Parent,IDNo, ParentIDNo, Clearance,sortkey) as
(Select ChildCategory, Category, IDNo, ParentIDNo,Clearance
,CAST('.'+CAST(idno AS VARCHAR(Max))+'.' AS VARCHAR(max)) AS sortkey
From Category
where parentidno is null
Union All
Select Cat.ChildCategory, Cat.Category, Cat.IDNo, Cat.ParentIDNo ,Cat.Clearance
,STUFF(('.' + sortkey + CAST(CAST(cat.idno AS VARCHAR(max)) + '.' AS VARCHAR(max))
),1,1,'')
From Category Cat
Inner join CatList List
On cat.ParentIDNo = List.IDNO
)
select Parent,Child,Idno,ParentIDNo,Clearance
from CatList
ORDER BY sortkey
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 12, 2011 at 5:36 pm
Ibe be .....d. It works. I am going to spend time tonight trying to understand the changes. I have no idea what Stuff does. But that is where I am going to start.
Thanks again. I really do appreciate the assitance especially on a Fri night.
pat
August 15, 2011 at 10:01 am
You're welcome. Stuff is concatenating the sequence of the hierarchy into a string.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 15, 2011 at 5:13 pm
The topic that just won't die.
The latest request is to have the nodes of the various tress in alphabetical order.
So I think I have to modify the SoryKey to include the ChildCategory. But everytime I try it, the Sortkey becomes NULL.
Here is the query, again.
With CatList
(Child, Parent,IDNo, ParentIDNo, Clearance,sortkey) as
(Select ChildCategory, Category, IDNo, ParentIDNo,Clearance ,
CAST('.'+CAST(idno AS VARCHAR(Max))+'.' AS VARCHAR(max)) AS sortkey
From Category
where parentidno is null
Union All
Select Cat.ChildCategory, Cat.Category, Cat.IDNo, Cat.ParentIDNo ,Cat.Clearance
STUFF(('.' + sortkey + CAST(CAST(cat.idno AS VARCHAR(max)) + '.' AS VARCHAR(max)) ),1,1,'')
From Category Cat
Inner join CatList List
On cat.ParentIDNo = List.IDNO)
select Parent,Child,Idno,ParentIDNo,Clearance
from CatList
ORDER BY sortkey
I tried this first:
With CatList
(Child, Parent,IDNo, ParentIDNo, Clearance,sortkey) as
(Select ChildCategory, Category, IDNo, ParentIDNo,Clearance ,
CAST('.'+CAST(idno + ChildCategory AS VARCHAR(Max))+'.' AS VARCHAR(max)) AS sortkey
From Category
where parentidno is null
Union All
Select Cat.ChildCategory, Cat.Category, Cat.IDNo, Cat.ParentIDNo ,Cat.Clearance
STUFF(('.' + sortkey + CAST(CAST(cat.idno+Cat.ChildCategory AS VARCHAR(max)) + '.' AS VARCHAR(max)) ),1,1,'')
From Category Cat
Inner join CatList List
On cat.ParentIDNo = List.IDNO)
select Parent,Child,Idno,ParentIDNo,Clearance
from CatList
ORDER BY sortkey
Next I tried:
With CatList
(Child, Parent,IDNo, ParentIDNo, Clearance,sortkey) as
(Select ChildCategory, Category, IDNo, ParentIDNo,Clearance ,
CAST('.'+CAST(idno AS VARCHAR(Max))+'.' AS VARCHAR(max))+ChildCategory AS sortkey
From Category
where parentidno is null
Union All
Select Cat.ChildCategory, Cat.Category, Cat.IDNo, Cat.ParentIDNo ,Cat.Clearance
STUFF(('.' + sortkey + CAST(CAST(cat.idno AS VARCHAR(max)) + '.' AS VARCHAR(max)) ),1,1,'')+Cat.ChildCategory
From Category Cat
Inner join CatList List
On cat.ParentIDNo = List.IDNO)
select Parent,Child,Idno,ParentIDNo,Clearance
from CatList
ORDER BY sortkey
Neither worked.
Thanks in advance for your assisatance.
pat
August 15, 2011 at 6:37 pm
Got it. I replaced the IDNo in the sortkey with ChildCategory and Category. If they were not encased in IsNull then the sort key would return NULL.
With CatList (Child, Parent,IDNo, ParentIDNo, Clearance,sortkey) as
(Select ChildCategory, Category, IDNo, ParentIDNo,Clearance ,
CAST('.'+CAST(isnull(ChildCategory,
isnull(Category,'')) AS VARCHAR(Max))+'.' AS VARCHAR(max)) AS sortkey
From Category
where parentidno is null
Union All
Select Cat.ChildCategory, Cat.Category, Cat.IDNo, Cat.ParentIDNo ,Cat.Clearance,
STUFF(('.' + sortkey + CAST(CAST(isnull(cat.ChildCategory,
isnull(Category,'')) AS VARCHAR(max)) + '.' AS VARCHAR(max)) ),1,1,'')
From Category Cat
Inner join CatList List
On cat.ParentIDNo = List.IDNO)
select Parent,Child,Idno,ParentIDNo,Clearance, sortkey from CatList
ORDER BY sortkey
August 16, 2011 at 8:13 am
Good to hear.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply