2 Tables 1 recursive
-
Hello I am back everyone,
with a new prob hoping anyone can help as my brain is little to small to handle this,
I have 2 tables, 1 order table and 1 parts table
order table is like this:
create table #orders
(
orderid int,
part varchar(50)
)
and 2nd table is called part
create table #parts
(
partheader int,
childpart varchar(50)
)
insert some stuff:
insert into #Orders(orderid,part)
values(1,'bike')
insert into #Orders(orderid,part)
values(2,'rollerskates')
insert into #Orders(orderid,part)
values(3,'scooter')
insert into #parts(partheader,childpart)
values('bike','wheels')
insert into #parts(partheader,childpart)
values('bike','chains')
insert into #parts(partheader,childpart)
values('chains','metal')
insert into #parts(partheader,childpart)
values('scooter','wheels')
insert into #parts(partheader,childpart)
values('scooter','metal')
ok so looking at the 2 tables, I am trying to accomplish 2 things, I think I need a recursive like at my last post, BUT, 1 thing needs to happen, I need it in a list rather than a tree, like so example of bike:
Bike
wheels
chains
Metal
so from looking above, I have a order table, then using recursive to loop the Parts table to get all the children and put it into one columns... sadly dont know how, any help would be great.
thanks in advanced 🙂
PS for my select, I will make sure I use an alias besides a and b 🙂 but more descriptive.
-
This should be what you're looking for... 🙂
IF OBJECT_ID('tempdb..#orders', 'U') IS NOT NULL
DROP TABLE #orders;
create table #orders (
orderid int,
part varchar(50)
);
IF OBJECT_ID('tempdb..#parts', 'U') IS NOT NULL
DROP TABLE #parts;
create table #parts (
partheader VARCHAR(50),
childpart varchar(50)
);
INSERTINTO #orders (orderid,part) VALUES
(1,'bike'),
(2,'rollerskates'),
(3,'scooter');
INSERT INTO #parts(partheader,childpart) VALUES
('bike','wheels'),
('bike','chains'),
('chains','metal'),
('scooter','wheels'),
('scooter','metal');
WITH
cte_PartsOrder AS (
SELECT
o.orderid,
o.part,
node = 1
FROM
#orders o
UNION ALL
SELECT
po.orderid,
p.childpart,
node = po.Node + 1
FROM
#parts p
JOIN cte_PartsOrder po
ON p.partheader = po.part
)
SELECT
po.orderid,
po.part
FROM
cte_PartsOrder po
ORDER BY
po.orderid,
po.node
;
Results...
orderid part
----------- --------------------------------------------------
1 bike
1 wheels
1 chains
1 metal
2 rollerskates
3 scooter
3 wheels
3 metal
-
Borrowing heavily from Jason's fine post and making a slight change to how the data is constructed so that people still using 2005 can easily play along if they need to, you can easily take this up a notch if you so desire. We can easily produce an indented and "sorted-as-expected" list just by changing Jason's "Node" to "HLevel" and adding a "SortPath" construct to the rCTE and then using those two things to control the indentation and sort order, respectively.
Of course, if you just need the list as you say, then Jason's code absolutely does the trick.
Here's the whole shebang...
--==============================================================================
-- Create the test table. This is NOT a Part of the solution.
-- We're just creating test data to demonstrate with.
--==============================================================================
--===== If the test tables already exist, drop them to make reruns easier.
IF OBJECT_ID('tempdb..#Orders', 'U') IS NOT NULL DROP TABLE #Orders;
IF OBJECT_ID('tempdb..#Parts' , 'U') IS NOT NULL DROP TABLE #Parts;
--===== Create the test tables
CREATE TABLE #Orders
(
OrderID INT NOT NULL PRIMARY KEY CLUSTERED
,Part VARCHAR(50)
)
;
CREATE TABLE #Parts
(
PartHeader VARCHAR(50)
,ChildPart VARCHAR(50)
)
;
--===== Populate the test tables with test data.
-- Made it compatible with 2005 and up
INSERT INTO #Orders
(OrderID,Part)
SELECT 1,'Bike' UNION ALL
SELECT 2,'Rollerskates' UNION ALL
SELECT 3,'Scooter'
;
INSERT INTO #Parts
(PartHeader,ChildPart)
SELECT 'Bike' ,'Wheels' UNION ALL
SELECT 'Bike' ,'Chains' UNION ALL
SELECT 'Chains' ,'Metal' UNION ALL
SELECT 'Scooter' ,'Wheels' UNION ALL
SELECT 'Scooter' ,'Metal'
;
--==============================================================================
-- Solve the hierarchical traversal and display problem.
-- Note the creation and use of the SortPath column to control the order
-- of the final output.
--==============================================================================
WITH
cte_hierarchy AS
(--==== Create the first level of the hierarhy for #Orders
SELECT o.OrderID
,o.Part
,HLevel = 1
,SortPath = CAST(o.Part AS VARCHAR(8000))
FROM #Orders o
UNION ALL
--==== Iterate through each level of the Parts hierarchy found in #Parts
-- to build the HLevel and SortPath for each node.
SELECT cte.OrderID
,p.ChildPart
,HLevel = cte.HLevel + 1
,SortPath = CAST(cte.SortPath + '/'+ p.ChildPart AS VARCHAR(8000))
FROM #Parts p
JOIN cte_hierarchy cte ON p.PartHeader = cte.Part
) --=== Display a nicely indented and sorted-as-expected hierarchy
SELECT h.OrderID
,Part = SPACE(4*(h.HLevel-1))+h.Part
,h.HLevel --Comment this line out if you don't want it to appear
,h.SortPath --Comment this line out if you don't want it to appear
FROM cte_hierarchy h
ORDER BY h.OrderID, h.SortPath
;
That produces the following output (recommend the Grid Mode)...
OrderID Part HLevel SortPath
----------- ------------- ------ -----------------
1 Bike 1 Bike
1 Chains 2 Bike/Chains
1 Metal 3 Bike/Chains/Metal
1 Wheels 2 Bike/Wheels
2 Rollerskates 1 Rollerskates
3 Scooter 1 Scooter
3 Metal 2 Scooter/Metal
3 Wheels 2 Scooter/Wheels
Here's an article that explains the modifications that I made to Jason's good code...
http://www.sqlservercentral.com/articles/T-SQL/72503/
If the tables you're dealing with are going to get large, you may want to consider the use of "Nested Sets". Here are a couple of article to help you really take things over the top for hierarchies if you need to.
http://www.sqlservercentral.com/articles/Hierarchy/94040/
http://www.sqlservercentral.com/articles/T-SQL/94570/
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff Moden (12/19/2015)
Here's an article that explains the modifications that I made to Jason's good code...
http://www.sqlservercentral.com/articles/T-SQL/72503/
If the tables you're dealing with are going to get large, you may want to consider the use of "Nested Sets". Here are a couple of article to help you really take things over the top for hierarchies if you need to.
+8 For the nested sets idea.
On that topic... Not sure if I've said it directly before... Thank you for "Hierarchies on Steroids #1: Convert an Adjacency List to Nested Sets"! I'd been aware of the Nested Sets Model and how much better it at handling hierarchies in SQL than stand alone adjacency list for years, but had basically give up on it simply because it look so damned long to calculate the left & right bowers. That article offered the first truly usable (IMO) solution to the problem.
The following chunk of code still blows my mind every time I look at it....
UPDATE h
SET @LeftBower = LeftBower = 2 * NodeNumber - HLevel,
h.NodeCount = downline.NodeCount,
h.RightBower = (downline.NodeCount - 1) * 2 + @LeftBower + 1
FROM dbo.Hierarchy h
JOIN cteCountDownlines downline
ON h.EmployeeID = downline.EmployeeID
;
Also... Thank you for the kind words above. Coming from you, I take it as a huge compliment.
-
I'm humbled. Thank you very much for your comments.
It's not my job to judge but you very well deserve any and all compliments. Your now familiar "Smokin' Monkey" avatar has become a symbol of frequent, accurate, and helpful posts. My hat's off to you, good Sir!
And, to be honest, all I did was ride on your good code on this post. You did all the hard work. I just played the part of "cleaning lady".
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Extremely kind words sir... Thank you again!
As for the Monkey... They were all out of the non smoking ones when I placed my order...
-
Oooooh! I could have a lot of fun with that...
"Trained Code Monkey! Faithful and Agile friend. Never code alone again. Likes all sorts of code. Not particular about readability, scalability, or performance. Can be taught to whine to the boss during code reviews and justify any technique but we didn't tell you that. Only $14.99. But wait... don't order yet. For just an additional $5.00, we'll train it to throw your DBA under the bus when you get behind on schedule."
😀
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I seem to recall reading something about beer popsicles... Ever see a drunk monkey with brain freeze? 😉
(I haven't... But I'd like to... and I don't care if that makes me a bad person...)
-
Jason A. Long (12/20/2015)
I seem to recall reading something about beer popsicles... Ever see a drunk monkey with brain freeze? 😉(I haven't... But I'd like to... and I don't care if that makes me a bad person...)
I have friends that resemble that after they've been drinking. Either that or I need to stay away from the zoo after I have. 😀
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply