SQL Server recursive self join
2
I have a simple categories table as with the following columns:
- Id
- Name
- ParentId
So, an infinite amount of Categories can be the child of a category. Take for example the following hierarchy:
I want, in a simple query that returns the category "Business Laptops" to also return a column with all it's parents, comma separator or something:
Or take the following example:
sql
sql-server recursion edited Mar 22 '17 at 15:10
marc_s
576k12811111258
asked Mar 22 '17 at 14:58
CarelCarel
88361850
add a comment |
2
I have a simple categories table as with the following columns:
- Id
- Name
- ParentId
So, an infinite amount of Categories can be the child of a category. Take for example the following hierarchy:
I want, in a simple query that returns the category "Business Laptops" to also return a column with all it's parents, comma separator or something:
Or take the following example:
sql
sql-server recursion edited Mar 22 '17 at 15:10
marc_s
576k12811111258
asked Mar 22 '17 at 14:58
CarelCarel
88361850
Have a look: dba.stackexchange.com/questions/160924/…
– McNets
Mar 22 '17 at 15:08
One important clarifying question: Will a category always only have one parent?
– Laughing Vergil
Mar 22 '17 at 15:09
@LaughingVergil: It will never have more than one direct parent, but it could also have no parent.
– Carel
Mar 23 '17 at 8:09
add a comment |
2
2
2
I have a simple categories table as with the following columns:
- Id
- Name
- ParentId
So, an infinite amount of Categories can be the child of a category. Take for example the following hierarchy:
I want, in a simple query that returns the category "Business Laptops" to also return a column with all it's parents, comma separator or something:
Or take the following example:
sql
sql-server recursion edited Mar 22 '17 at 15:10
marc_s
576k12811111258
asked Mar 22 '17 at 14:58
CarelCarel
88361850
I have a simple categories table as with the following columns:
- Id
- Name
- ParentId
So, an infinite amount of Categories can be the child of a category. Take for example the following hierarchy:
I want, in a simple query that returns the category "Business Laptops" to also return a column with all it's parents, comma separator or something:
Or take the following example:
sql
sql-server recursion sql
sql-server recursion edited Mar 22 '17 at 15:10
marc_s
576k12811111258
asked Mar 22 '17 at 14:58
CarelCarel
88361850
edited Mar 22 '17 at 15:10
marc_s
576k12811111258
asked Mar 22 '17 at 14:58
CarelCarel
88361850
edited Mar 22 '17 at 15:10
marc_s
576k12811111258
edited Mar 22 '17 at 15:10
marc_s
576k12811111258
edited Mar 22 '17 at 15:10
marc_s
576k12811111258
576k12811111258
asked Mar 22 '17 at 14:58
CarelCarel
88361850
asked Mar 22 '17 at 14:58
CarelCarel
88361850
asked Mar 22 '17 at 14:58
CarelCarel
88361850
88361850
Have a look: dba.stackexchange.com/questions/160924/…
– McNets
Mar 22 '17 at 15:08
One important clarifying question: Will a category always only have one parent?
– Laughing Vergil
Mar 22 '17 at 15:09
@LaughingVergil: It will never have more than one direct parent, but it could also have no parent.
– Carel
Mar 23 '17 at 8:09
add a comment |
Have a look: dba.stackexchange.com/questions/160924/…
– McNets
Mar 22 '17 at 15:08
One important clarifying question: Will a category always only have one parent?
– Laughing Vergil
Mar 22 '17 at 15:09
@LaughingVergil: It will never have more than one direct parent, but it could also have no parent.
– Carel
Mar 23 '17 at 8:09
Have a look: dba.stackexchange.com/questions/160924/…
– McNets
Mar 22 '17 at 15:08
Have a look: dba.stackexchange.com/questions/160924/…
– McNets
Mar 22 '17 at 15:08
One important clarifying question: Will a category always only have one parent?
– Laughing Vergil
Mar 22 '17 at 15:09
One important clarifying question: Will a category always only have one parent?
– Laughing Vergil
Mar 22 '17 at 15:09
@LaughingVergil: It will never have more than one direct parent, but it could also have no parent.
– Carel
Mar 23 '17 at 8:09
@LaughingVergil: It will never have more than one direct parent, but it could also have no parent.
– Carel
Mar 23 '17 at 8:09
add a comment |
1 Answer
1
active
oldest
votes
9
Recursive cte to the rescue....
Create and populate sample table (Please save us this step in your future questions):
DECLARE @T as table
(
id int,
name varchar(100),
parent_id int
)
INSERT INTO @T VALUES
(1, 'A', NULL),
(2, 'A.1', 1),
(3, 'A.2', 1),
(4, 'A.1.1', 2),
(5, 'B', NULL),
(6, 'B.1', 5),
(7, 'B.1.1', 6),
(8, 'B.2', 5),
(9, 'A.1.1.1', 4),
(10, 'A.1.1.2', 4)
The cte:
;WITH CTE AS
(
SELECT id, name, name as path, parent_id
FROM @T
WHERE parent_id IS NULL
UNION ALL
SELECT t.id, t.name, cast(cte.path +','+ t.name as varchar(100)), t.parent_id
FROM @T t
INNER JOIN CTE ON t.parent_id = CTE.id
)
The query:
SELECT id, name, path
FROM CTE
Results:
id name path
1 A A
5 B B
6 B.1 B,B.1
8 B.2 B,B.2
7 B.1.1 B,B.1,B.1.1
2 A.1 A,A.1
3 A.2 A,A.2
4 A.1.1 A,A.1,A.1.1
9 A.1.1.1 A,A.1,A.1.1,A.1.1.1
10 A.1.1.2 A,A.1,A.1.1,A.1.1.2
See online demo on rextester
answered Mar 22 '17 at 15:07
Zohar PeledZohar Peled
54k73273
Shouldn't the query beSELECT id, name, path FROM CTE?
– Mihai Ovidiu Drăgoi
Mar 22 '17 at 15:20
@MihaiOvidiuDrăgoi correct, I've played with it a liitle and forgot to remove theas. fixed now.
– Zohar Peled
Mar 22 '17 at 15:22
1
@ZoharPeled: awesome. I wasn't expecting such a complete answer. I will certainly in the future include sample data. Following is the query in action with some of my actual data: link
– Carel
Mar 23 '17 at 10:24
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f42955335%2fsql-server-recursive-self-join%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
9
Recursive cte to the rescue....
Create and populate sample table (Please save us this step in your future questions):
DECLARE @T as table
(
id int,
name varchar(100),
parent_id int
)
INSERT INTO @T VALUES
(1, 'A', NULL),
(2, 'A.1', 1),
(3, 'A.2', 1),
(4, 'A.1.1', 2),
(5, 'B', NULL),
(6, 'B.1', 5),
(7, 'B.1.1', 6),
(8, 'B.2', 5),
(9, 'A.1.1.1', 4),
(10, 'A.1.1.2', 4)
The cte:
;WITH CTE AS
(
SELECT id, name, name as path, parent_id
FROM @T
WHERE parent_id IS NULL
UNION ALL
SELECT t.id, t.name, cast(cte.path +','+ t.name as varchar(100)), t.parent_id
FROM @T t
INNER JOIN CTE ON t.parent_id = CTE.id
)
The query:
SELECT id, name, path
FROM CTE
Results:
id name path
1 A A
5 B B
6 B.1 B,B.1
8 B.2 B,B.2
7 B.1.1 B,B.1,B.1.1
2 A.1 A,A.1
3 A.2 A,A.2
4 A.1.1 A,A.1,A.1.1
9 A.1.1.1 A,A.1,A.1.1,A.1.1.1
10 A.1.1.2 A,A.1,A.1.1,A.1.1.2
See online demo on rextester
answered Mar 22 '17 at 15:07
Zohar PeledZohar Peled
54k73273
Shouldn't the query beSELECT id, name, path FROM CTE?
– Mihai Ovidiu Drăgoi
Mar 22 '17 at 15:20
@MihaiOvidiuDrăgoi correct, I've played with it a liitle and forgot to remove theas. fixed now.
– Zohar Peled
Mar 22 '17 at 15:22
1
@ZoharPeled: awesome. I wasn't expecting such a complete answer. I will certainly in the future include sample data. Following is the query in action with some of my actual data: link
– Carel
Mar 23 '17 at 10:24
add a comment |
9
Recursive cte to the rescue....
Create and populate sample table (Please save us this step in your future questions):
DECLARE @T as table
(
id int,
name varchar(100),
parent_id int
)
INSERT INTO @T VALUES
(1, 'A', NULL),
(2, 'A.1', 1),
(3, 'A.2', 1),
(4, 'A.1.1', 2),
(5, 'B', NULL),
(6, 'B.1', 5),
(7, 'B.1.1', 6),
(8, 'B.2', 5),
(9, 'A.1.1.1', 4),
(10, 'A.1.1.2', 4)
The cte:
;WITH CTE AS
(
SELECT id, name, name as path, parent_id
FROM @T
WHERE parent_id IS NULL
UNION ALL
SELECT t.id, t.name, cast(cte.path +','+ t.name as varchar(100)), t.parent_id
FROM @T t
INNER JOIN CTE ON t.parent_id = CTE.id
)
The query:
SELECT id, name, path
FROM CTE
Results:
id name path
1 A A
5 B B
6 B.1 B,B.1
8 B.2 B,B.2
7 B.1.1 B,B.1,B.1.1
2 A.1 A,A.1
3 A.2 A,A.2
4 A.1.1 A,A.1,A.1.1
9 A.1.1.1 A,A.1,A.1.1,A.1.1.1
10 A.1.1.2 A,A.1,A.1.1,A.1.1.2
See online demo on rextester
answered Mar 22 '17 at 15:07
Zohar PeledZohar Peled
54k73273
Shouldn't the query beSELECT id, name, path FROM CTE?
– Mihai Ovidiu Drăgoi
Mar 22 '17 at 15:20
@MihaiOvidiuDrăgoi correct, I've played with it a liitle and forgot to remove theas. fixed now.
– Zohar Peled
Mar 22 '17 at 15:22
1
@ZoharPeled: awesome. I wasn't expecting such a complete answer. I will certainly in the future include sample data. Following is the query in action with some of my actual data: link
– Carel
Mar 23 '17 at 10:24
add a comment |
9
9
9
Recursive cte to the rescue....
Create and populate sample table (Please save us this step in your future questions):
DECLARE @T as table
(
id int,
name varchar(100),
parent_id int
)
INSERT INTO @T VALUES
(1, 'A', NULL),
(2, 'A.1', 1),
(3, 'A.2', 1),
(4, 'A.1.1', 2),
(5, 'B', NULL),
(6, 'B.1', 5),
(7, 'B.1.1', 6),
(8, 'B.2', 5),
(9, 'A.1.1.1', 4),
(10, 'A.1.1.2', 4)
The cte:
;WITH CTE AS
(
SELECT id, name, name as path, parent_id
FROM @T
WHERE parent_id IS NULL
UNION ALL
SELECT t.id, t.name, cast(cte.path +','+ t.name as varchar(100)), t.parent_id
FROM @T t
INNER JOIN CTE ON t.parent_id = CTE.id
)
The query:
SELECT id, name, path
FROM CTE
Results:
id name path
1 A A
5 B B
6 B.1 B,B.1
8 B.2 B,B.2
7 B.1.1 B,B.1,B.1.1
2 A.1 A,A.1
3 A.2 A,A.2
4 A.1.1 A,A.1,A.1.1
9 A.1.1.1 A,A.1,A.1.1,A.1.1.1
10 A.1.1.2 A,A.1,A.1.1,A.1.1.2
See online demo on rextester
answered Mar 22 '17 at 15:07
Zohar PeledZohar Peled
54k73273
Recursive cte to the rescue....
Create and populate sample table (Please save us this step in your future questions):
DECLARE @T as table
(
id int,
name varchar(100),
parent_id int
)
INSERT INTO @T VALUES
(1, 'A', NULL),
(2, 'A.1', 1),
(3, 'A.2', 1),
(4, 'A.1.1', 2),
(5, 'B', NULL),
(6, 'B.1', 5),
(7, 'B.1.1', 6),
(8, 'B.2', 5),
(9, 'A.1.1.1', 4),
(10, 'A.1.1.2', 4)
The cte:
;WITH CTE AS
(
SELECT id, name, name as path, parent_id
FROM @T
WHERE parent_id IS NULL
UNION ALL
SELECT t.id, t.name, cast(cte.path +','+ t.name as varchar(100)), t.parent_id
FROM @T t
INNER JOIN CTE ON t.parent_id = CTE.id
)
The query:
SELECT id, name, path
FROM CTE
Results:
id name path
1 A A
5 B B
6 B.1 B,B.1
8 B.2 B,B.2
7 B.1.1 B,B.1,B.1.1
2 A.1 A,A.1
3 A.2 A,A.2
4 A.1.1 A,A.1,A.1.1
9 A.1.1.1 A,A.1,A.1.1,A.1.1.1
10 A.1.1.2 A,A.1,A.1.1,A.1.1.2
See online demo on rextester
answered Mar 22 '17 at 15:07
Zohar PeledZohar Peled
54k73273
edited Mar 22 '17 at 15:21
answered Mar 22 '17 at 15:07
Zohar PeledZohar Peled
54k73273
answered Mar 22 '17 at 15:07
Zohar PeledZohar Peled
54k73273
answered Mar 22 '17 at 15:07
Zohar PeledZohar Peled
54k73273
54k73273
Shouldn't the query beSELECT id, name, path FROM CTE?
– Mihai Ovidiu Drăgoi
Mar 22 '17 at 15:20
@MihaiOvidiuDrăgoi correct, I've played with it a liitle and forgot to remove theas. fixed now.
– Zohar Peled
Mar 22 '17 at 15:22
1
@ZoharPeled: awesome. I wasn't expecting such a complete answer. I will certainly in the future include sample data. Following is the query in action with some of my actual data: link
– Carel
Mar 23 '17 at 10:24
add a comment |
Shouldn't the query beSELECT id, name, path FROM CTE?
– Mihai Ovidiu Drăgoi
Mar 22 '17 at 15:20
@MihaiOvidiuDrăgoi correct, I've played with it a liitle and forgot to remove theas. fixed now.
– Zohar Peled
Mar 22 '17 at 15:22
1
@ZoharPeled: awesome. I wasn't expecting such a complete answer. I will certainly in the future include sample data. Following is the query in action with some of my actual data: link
– Carel
Mar 23 '17 at 10:24
Shouldn't the query be
SELECT id, name, path FROM CTE?– Mihai Ovidiu Drăgoi
Mar 22 '17 at 15:20
Shouldn't the query be
SELECT id, name, path FROM CTE?– Mihai Ovidiu Drăgoi
Mar 22 '17 at 15:20
@MihaiOvidiuDrăgoi correct, I've played with it a liitle and forgot to remove the
as. fixed now.– Zohar Peled
Mar 22 '17 at 15:22
@MihaiOvidiuDrăgoi correct, I've played with it a liitle and forgot to remove the
as. fixed now.– Zohar Peled
Mar 22 '17 at 15:22
1
1
@ZoharPeled: awesome. I wasn't expecting such a complete answer. I will certainly in the future include sample data. Following is the query in action with some of my actual data: link
– Carel
Mar 23 '17 at 10:24
@ZoharPeled: awesome. I wasn't expecting such a complete answer. I will certainly in the future include sample data. Following is the query in action with some of my actual data: link
– Carel
Mar 23 '17 at 10:24
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f42955335%2fsql-server-recursive-self-join%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Have a look: dba.stackexchange.com/questions/160924/…
– McNets
Mar 22 '17 at 15:08
One important clarifying question: Will a category always only have one parent?
– Laughing Vergil
Mar 22 '17 at 15:09
@LaughingVergil: It will never have more than one direct parent, but it could also have no parent.
– Carel
Mar 23 '17 at 8:09