Mixing
categorize records of table in SQL?
I have a table with increasing records.it contains some columns including id1, id2. I want insert a column to categorize these records in this way:
For example if id1=1 in relate with id2=2 be in one category
If id1=3 in relate with id2=2 all three ids 1, 2, 3 group in same category
Pk | id1 | id2 | category
--------+---------+---------+-----------
1 | 1111 | 2222 | 1
2 | 2222 | 3333 | 1
3 | 3333 | 1111 | 1
4 | 4444 | 5555 | 1
5 | 2222 | 1111 | 1
6 | 5555 | 1111 | 1
7 | 6666 | 8888 | 2
8 | 7777 | 9999 | 3
And if any new record adds to table it get a group and updates old groups. For example if new record was like below, change the category of 7th row to 1
Pk | id1 | id2 | category
--------+---------+---------+-----------
7 | 6666 | 8888 | 1
8 | 7777 | 9999 | 3
9 | 8888 | 1111 | 1
or instead of inserting a column in this table, create another table with id and category for realizing each id's category.
By this way, I want understand networks between different ID's.
sql
sql-server edited Jan 1 at 9:14
marc_s
580k13011181266
asked Jan 1 at 5:35
samensamen
157
|
show 1 more comment
I have a table with increasing records.it contains some columns including id1, id2. I want insert a column to categorize these records in this way:
For example if id1=1 in relate with id2=2 be in one category
If id1=3 in relate with id2=2 all three ids 1, 2, 3 group in same category
Pk | id1 | id2 | category
--------+---------+---------+-----------
1 | 1111 | 2222 | 1
2 | 2222 | 3333 | 1
3 | 3333 | 1111 | 1
4 | 4444 | 5555 | 1
5 | 2222 | 1111 | 1
6 | 5555 | 1111 | 1
7 | 6666 | 8888 | 2
8 | 7777 | 9999 | 3
And if any new record adds to table it get a group and updates old groups. For example if new record was like below, change the category of 7th row to 1
Pk | id1 | id2 | category
--------+---------+---------+-----------
7 | 6666 | 8888 | 1
8 | 7777 | 9999 | 3
9 | 8888 | 1111 | 1
or instead of inserting a column in this table, create another table with id and category for realizing each id's category.
By this way, I want understand networks between different ID's.
sql
sql-server edited Jan 1 at 9:14
marc_s
580k13011181266
asked Jan 1 at 5:35
samensamen
157
I don't follow your question, and I don't understand why4444 -> 5555is in category 1. You might want to explain more about what you are doing here.
– Tim Biegeleisen
Jan 1 at 5:49
because id=1 have relation with id=5 in 6th row. so both of them have same category. and id=4 have relation with id=5. so id=4 place in category=1 with id=4 and id=5
– samen
Jan 1 at 5:59
You might want to look into using a graph based database, such as GraphQL. But, tell us which version of SQL you are using (e.g. MySQL, SQL Server, Oracle, etc.).
– Tim Biegeleisen
Jan 1 at 6:00
Microsoft SQL server management studio 17
– samen
Jan 1 at 6:02
is it possible at all?
– samen
Jan 1 at 6:10
|
show 1 more comment
I have a table with increasing records.it contains some columns including id1, id2. I want insert a column to categorize these records in this way:
For example if id1=1 in relate with id2=2 be in one category
If id1=3 in relate with id2=2 all three ids 1, 2, 3 group in same category
Pk | id1 | id2 | category
--------+---------+---------+-----------
1 | 1111 | 2222 | 1
2 | 2222 | 3333 | 1
3 | 3333 | 1111 | 1
4 | 4444 | 5555 | 1
5 | 2222 | 1111 | 1
6 | 5555 | 1111 | 1
7 | 6666 | 8888 | 2
8 | 7777 | 9999 | 3
And if any new record adds to table it get a group and updates old groups. For example if new record was like below, change the category of 7th row to 1
Pk | id1 | id2 | category
--------+---------+---------+-----------
7 | 6666 | 8888 | 1
8 | 7777 | 9999 | 3
9 | 8888 | 1111 | 1
or instead of inserting a column in this table, create another table with id and category for realizing each id's category.
By this way, I want understand networks between different ID's.
sql
sql-server edited Jan 1 at 9:14
marc_s
580k13011181266
asked Jan 1 at 5:35
samensamen
157
I have a table with increasing records.it contains some columns including id1, id2. I want insert a column to categorize these records in this way:
For example if id1=1 in relate with id2=2 be in one category
If id1=3 in relate with id2=2 all three ids 1, 2, 3 group in same category
Pk | id1 | id2 | category
--------+---------+---------+-----------
1 | 1111 | 2222 | 1
2 | 2222 | 3333 | 1
3 | 3333 | 1111 | 1
4 | 4444 | 5555 | 1
5 | 2222 | 1111 | 1
6 | 5555 | 1111 | 1
7 | 6666 | 8888 | 2
8 | 7777 | 9999 | 3
And if any new record adds to table it get a group and updates old groups. For example if new record was like below, change the category of 7th row to 1
Pk | id1 | id2 | category
--------+---------+---------+-----------
7 | 6666 | 8888 | 1
8 | 7777 | 9999 | 3
9 | 8888 | 1111 | 1
or instead of inserting a column in this table, create another table with id and category for realizing each id's category.
By this way, I want understand networks between different ID's.
sql
sql-server sql
sql-server edited Jan 1 at 9:14
marc_s
580k13011181266
asked Jan 1 at 5:35
samensamen
157
edited Jan 1 at 9:14
marc_s
580k13011181266
asked Jan 1 at 5:35
samensamen
157
edited Jan 1 at 9:14
marc_s
580k13011181266
edited Jan 1 at 9:14
marc_s
580k13011181266
edited Jan 1 at 9:14
marc_s
580k13011181266
580k13011181266
asked Jan 1 at 5:35
samensamen
157
asked Jan 1 at 5:35
samensamen
157
asked Jan 1 at 5:35
samensamen
157
157
I don't follow your question, and I don't understand why4444 -> 5555is in category 1. You might want to explain more about what you are doing here.
– Tim Biegeleisen
Jan 1 at 5:49
because id=1 have relation with id=5 in 6th row. so both of them have same category. and id=4 have relation with id=5. so id=4 place in category=1 with id=4 and id=5
– samen
Jan 1 at 5:59
You might want to look into using a graph based database, such as GraphQL. But, tell us which version of SQL you are using (e.g. MySQL, SQL Server, Oracle, etc.).
– Tim Biegeleisen
Jan 1 at 6:00
Microsoft SQL server management studio 17
– samen
Jan 1 at 6:02
is it possible at all?
– samen
Jan 1 at 6:10
|
show 1 more comment
I don't follow your question, and I don't understand why4444 -> 5555is in category 1. You might want to explain more about what you are doing here.
– Tim Biegeleisen
Jan 1 at 5:49
because id=1 have relation with id=5 in 6th row. so both of them have same category. and id=4 have relation with id=5. so id=4 place in category=1 with id=4 and id=5
– samen
Jan 1 at 5:59
You might want to look into using a graph based database, such as GraphQL. But, tell us which version of SQL you are using (e.g. MySQL, SQL Server, Oracle, etc.).
– Tim Biegeleisen
Jan 1 at 6:00
Microsoft SQL server management studio 17
– samen
Jan 1 at 6:02
is it possible at all?
– samen
Jan 1 at 6:10
I don't follow your question, and I don't understand why
4444 -> 5555 is in category 1. You might want to explain more about what you are doing here.– Tim Biegeleisen
Jan 1 at 5:49
I don't follow your question, and I don't understand why
4444 -> 5555 is in category 1. You might want to explain more about what you are doing here.– Tim Biegeleisen
Jan 1 at 5:49
because id=1 have relation with id=5 in 6th row. so both of them have same category. and id=4 have relation with id=5. so id=4 place in category=1 with id=4 and id=5
– samen
Jan 1 at 5:59
because id=1 have relation with id=5 in 6th row. so both of them have same category. and id=4 have relation with id=5. so id=4 place in category=1 with id=4 and id=5
– samen
Jan 1 at 5:59
You might want to look into using a graph based database, such as GraphQL. But, tell us which version of SQL you are using (e.g. MySQL, SQL Server, Oracle, etc.).
– Tim Biegeleisen
Jan 1 at 6:00
You might want to look into using a graph based database, such as GraphQL. But, tell us which version of SQL you are using (e.g. MySQL, SQL Server, Oracle, etc.).
– Tim Biegeleisen
Jan 1 at 6:00
Microsoft SQL server management studio 17
– samen
Jan 1 at 6:02
Microsoft SQL server management studio 17
– samen
Jan 1 at 6:02
is it possible at all?
– samen
Jan 1 at 6:10
is it possible at all?
– samen
Jan 1 at 6:10
|
show 1 more comment
2 Answers
2
active
oldest
votes
General graph walking is a bit painful using CTEs -- but possible. And there really aren't alternatives.
In SQL Server, you can maintain a list of visited nodes. This prevents infinite recursion. Unfortunately, this list is stored using a string.
So, this calculates the categories:
with t as (
select v.*
from (values (1, 1111, 2222),
(2, 2222, 3333),
(3, 3333, 1111),
(4, 4444, 5555),
(5, 2222, 1111),
(6, 5555, 1111),
(7, 6666, 8888),
(8, 7777, 9999)
) v(pk, id1, id2)
),
cte as (
select pk, id1, id1 as id2, convert(varchar(max), concat(',', id1, ',')) as visited
from t
union all
select cte.pk, cte.id1, t.id2, convert(varchar(max), concat(visited, t.id2, ','))
from cte join
t
on cte.id2 = t.id1
where cte.visited not like concat('%,', t.id2, ',%')
union all
select cte.pk, cte.id1, t.id1, convert(varchar(max), concat(visited, t.id1, ','))
from cte join
t
on cte.id2 = t.id2
where cte.visited not like concat('%,', t.id1, ',%')
)
select pk, id1, min(id2), dense_rank() over (order by min(id2))
from cte
group by pk, id1;
You can adapt this code to do an update (via a join on the primary key).
You can also incorporate this into a trigger or application to adjust the categories when new edges are added.
However, you should revise your data structure. You have a graph data structure, so you should have a table of ids and a table of edges. The categories represent disconnected subgraphs, and should be applied on the nodes not the edges.
Here is a db<>fiddle with the above code.
answered Jan 1 at 13:29
Gordon LinoffGordon Linoff
782k35310414
add a comment |
This pattern will help you I think
please customize it for yourself:
declare @t table(id int,parentId int,name varchar(20))
insert @t select 1, 0, 'Category1'
insert @t select 2, 0, 'Category2'
insert @t select 3, 1, 'Category3'
insert @t select 4 , 2, 'Category4'
insert @t select 5 , 1, 'Category5'
insert @t select 6 , 2, 'Category6'
insert @t select 7 , 3, 'Category7'
;
WITH tree (id, parentid, level, name, rn) as
(
SELECT id, parentid, 0 as level, name,
convert(varchar(max),right(row_number() over (order by id),10)) rn
FROM @t
WHERE parentid = 0
UNION ALL
SELECT c2.id, c2.parentid, tree.level + 1, c2.name,
rn
FROM @t c2
INNER JOIN tree ON tree.id = c2.parentid
)
SELECT *
FROM tree
order by RN
answered Jan 1 at 7:01
CodeManCodeMan
557311
you can use it in Trigger or ...
– CodeMan
Jan 1 at 7:03
by this way every id can have only one parent id. but in my database every id may have multiple parents.
– samen
Jan 1 at 9:01
I just copied my code for you, please customize it.
– CodeMan
Jan 1 at 11:03
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
});
}
});
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%2f53993226%2fcategorize-records-of-table-in-sql%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
General graph walking is a bit painful using CTEs -- but possible. And there really aren't alternatives.
In SQL Server, you can maintain a list of visited nodes. This prevents infinite recursion. Unfortunately, this list is stored using a string.
So, this calculates the categories:
with t as (
select v.*
from (values (1, 1111, 2222),
(2, 2222, 3333),
(3, 3333, 1111),
(4, 4444, 5555),
(5, 2222, 1111),
(6, 5555, 1111),
(7, 6666, 8888),
(8, 7777, 9999)
) v(pk, id1, id2)
),
cte as (
select pk, id1, id1 as id2, convert(varchar(max), concat(',', id1, ',')) as visited
from t
union all
select cte.pk, cte.id1, t.id2, convert(varchar(max), concat(visited, t.id2, ','))
from cte join
t
on cte.id2 = t.id1
where cte.visited not like concat('%,', t.id2, ',%')
union all
select cte.pk, cte.id1, t.id1, convert(varchar(max), concat(visited, t.id1, ','))
from cte join
t
on cte.id2 = t.id2
where cte.visited not like concat('%,', t.id1, ',%')
)
select pk, id1, min(id2), dense_rank() over (order by min(id2))
from cte
group by pk, id1;
You can adapt this code to do an update (via a join on the primary key).
You can also incorporate this into a trigger or application to adjust the categories when new edges are added.
However, you should revise your data structure. You have a graph data structure, so you should have a table of ids and a table of edges. The categories represent disconnected subgraphs, and should be applied on the nodes not the edges.
Here is a db<>fiddle with the above code.
answered Jan 1 at 13:29
Gordon LinoffGordon Linoff
782k35310414
add a comment |
General graph walking is a bit painful using CTEs -- but possible. And there really aren't alternatives.
In SQL Server, you can maintain a list of visited nodes. This prevents infinite recursion. Unfortunately, this list is stored using a string.
So, this calculates the categories:
with t as (
select v.*
from (values (1, 1111, 2222),
(2, 2222, 3333),
(3, 3333, 1111),
(4, 4444, 5555),
(5, 2222, 1111),
(6, 5555, 1111),
(7, 6666, 8888),
(8, 7777, 9999)
) v(pk, id1, id2)
),
cte as (
select pk, id1, id1 as id2, convert(varchar(max), concat(',', id1, ',')) as visited
from t
union all
select cte.pk, cte.id1, t.id2, convert(varchar(max), concat(visited, t.id2, ','))
from cte join
t
on cte.id2 = t.id1
where cte.visited not like concat('%,', t.id2, ',%')
union all
select cte.pk, cte.id1, t.id1, convert(varchar(max), concat(visited, t.id1, ','))
from cte join
t
on cte.id2 = t.id2
where cte.visited not like concat('%,', t.id1, ',%')
)
select pk, id1, min(id2), dense_rank() over (order by min(id2))
from cte
group by pk, id1;
You can adapt this code to do an update (via a join on the primary key).
You can also incorporate this into a trigger or application to adjust the categories when new edges are added.
However, you should revise your data structure. You have a graph data structure, so you should have a table of ids and a table of edges. The categories represent disconnected subgraphs, and should be applied on the nodes not the edges.
Here is a db<>fiddle with the above code.
answered Jan 1 at 13:29
Gordon LinoffGordon Linoff
782k35310414
add a comment |
General graph walking is a bit painful using CTEs -- but possible. And there really aren't alternatives.
In SQL Server, you can maintain a list of visited nodes. This prevents infinite recursion. Unfortunately, this list is stored using a string.
So, this calculates the categories:
with t as (
select v.*
from (values (1, 1111, 2222),
(2, 2222, 3333),
(3, 3333, 1111),
(4, 4444, 5555),
(5, 2222, 1111),
(6, 5555, 1111),
(7, 6666, 8888),
(8, 7777, 9999)
) v(pk, id1, id2)
),
cte as (
select pk, id1, id1 as id2, convert(varchar(max), concat(',', id1, ',')) as visited
from t
union all
select cte.pk, cte.id1, t.id2, convert(varchar(max), concat(visited, t.id2, ','))
from cte join
t
on cte.id2 = t.id1
where cte.visited not like concat('%,', t.id2, ',%')
union all
select cte.pk, cte.id1, t.id1, convert(varchar(max), concat(visited, t.id1, ','))
from cte join
t
on cte.id2 = t.id2
where cte.visited not like concat('%,', t.id1, ',%')
)
select pk, id1, min(id2), dense_rank() over (order by min(id2))
from cte
group by pk, id1;
You can adapt this code to do an update (via a join on the primary key).
You can also incorporate this into a trigger or application to adjust the categories when new edges are added.
However, you should revise your data structure. You have a graph data structure, so you should have a table of ids and a table of edges. The categories represent disconnected subgraphs, and should be applied on the nodes not the edges.
Here is a db<>fiddle with the above code.
answered Jan 1 at 13:29
Gordon LinoffGordon Linoff
782k35310414
General graph walking is a bit painful using CTEs -- but possible. And there really aren't alternatives.
In SQL Server, you can maintain a list of visited nodes. This prevents infinite recursion. Unfortunately, this list is stored using a string.
So, this calculates the categories:
with t as (
select v.*
from (values (1, 1111, 2222),
(2, 2222, 3333),
(3, 3333, 1111),
(4, 4444, 5555),
(5, 2222, 1111),
(6, 5555, 1111),
(7, 6666, 8888),
(8, 7777, 9999)
) v(pk, id1, id2)
),
cte as (
select pk, id1, id1 as id2, convert(varchar(max), concat(',', id1, ',')) as visited
from t
union all
select cte.pk, cte.id1, t.id2, convert(varchar(max), concat(visited, t.id2, ','))
from cte join
t
on cte.id2 = t.id1
where cte.visited not like concat('%,', t.id2, ',%')
union all
select cte.pk, cte.id1, t.id1, convert(varchar(max), concat(visited, t.id1, ','))
from cte join
t
on cte.id2 = t.id2
where cte.visited not like concat('%,', t.id1, ',%')
)
select pk, id1, min(id2), dense_rank() over (order by min(id2))
from cte
group by pk, id1;
You can adapt this code to do an update (via a join on the primary key).
You can also incorporate this into a trigger or application to adjust the categories when new edges are added.
However, you should revise your data structure. You have a graph data structure, so you should have a table of ids and a table of edges. The categories represent disconnected subgraphs, and should be applied on the nodes not the edges.
Here is a db<>fiddle with the above code.
answered Jan 1 at 13:29
Gordon LinoffGordon Linoff
782k35310414
answered Jan 1 at 13:29
Gordon LinoffGordon Linoff
782k35310414
answered Jan 1 at 13:29
Gordon LinoffGordon Linoff
782k35310414
answered Jan 1 at 13:29
Gordon LinoffGordon Linoff
782k35310414
782k35310414
add a comment |
add a comment |
This pattern will help you I think
please customize it for yourself:
declare @t table(id int,parentId int,name varchar(20))
insert @t select 1, 0, 'Category1'
insert @t select 2, 0, 'Category2'
insert @t select 3, 1, 'Category3'
insert @t select 4 , 2, 'Category4'
insert @t select 5 , 1, 'Category5'
insert @t select 6 , 2, 'Category6'
insert @t select 7 , 3, 'Category7'
;
WITH tree (id, parentid, level, name, rn) as
(
SELECT id, parentid, 0 as level, name,
convert(varchar(max),right(row_number() over (order by id),10)) rn
FROM @t
WHERE parentid = 0
UNION ALL
SELECT c2.id, c2.parentid, tree.level + 1, c2.name,
rn
FROM @t c2
INNER JOIN tree ON tree.id = c2.parentid
)
SELECT *
FROM tree
order by RN
answered Jan 1 at 7:01
CodeManCodeMan
557311
you can use it in Trigger or ...
– CodeMan
Jan 1 at 7:03
by this way every id can have only one parent id. but in my database every id may have multiple parents.
– samen
Jan 1 at 9:01
I just copied my code for you, please customize it.
– CodeMan
Jan 1 at 11:03
add a comment |
This pattern will help you I think
please customize it for yourself:
declare @t table(id int,parentId int,name varchar(20))
insert @t select 1, 0, 'Category1'
insert @t select 2, 0, 'Category2'
insert @t select 3, 1, 'Category3'
insert @t select 4 , 2, 'Category4'
insert @t select 5 , 1, 'Category5'
insert @t select 6 , 2, 'Category6'
insert @t select 7 , 3, 'Category7'
;
WITH tree (id, parentid, level, name, rn) as
(
SELECT id, parentid, 0 as level, name,
convert(varchar(max),right(row_number() over (order by id),10)) rn
FROM @t
WHERE parentid = 0
UNION ALL
SELECT c2.id, c2.parentid, tree.level + 1, c2.name,
rn
FROM @t c2
INNER JOIN tree ON tree.id = c2.parentid
)
SELECT *
FROM tree
order by RN
answered Jan 1 at 7:01
CodeManCodeMan
557311
you can use it in Trigger or ...
– CodeMan
Jan 1 at 7:03
by this way every id can have only one parent id. but in my database every id may have multiple parents.
– samen
Jan 1 at 9:01
I just copied my code for you, please customize it.
– CodeMan
Jan 1 at 11:03
add a comment |
This pattern will help you I think
please customize it for yourself:
declare @t table(id int,parentId int,name varchar(20))
insert @t select 1, 0, 'Category1'
insert @t select 2, 0, 'Category2'
insert @t select 3, 1, 'Category3'
insert @t select 4 , 2, 'Category4'
insert @t select 5 , 1, 'Category5'
insert @t select 6 , 2, 'Category6'
insert @t select 7 , 3, 'Category7'
;
WITH tree (id, parentid, level, name, rn) as
(
SELECT id, parentid, 0 as level, name,
convert(varchar(max),right(row_number() over (order by id),10)) rn
FROM @t
WHERE parentid = 0
UNION ALL
SELECT c2.id, c2.parentid, tree.level + 1, c2.name,
rn
FROM @t c2
INNER JOIN tree ON tree.id = c2.parentid
)
SELECT *
FROM tree
order by RN
answered Jan 1 at 7:01
CodeManCodeMan
557311
This pattern will help you I think
please customize it for yourself:
declare @t table(id int,parentId int,name varchar(20))
insert @t select 1, 0, 'Category1'
insert @t select 2, 0, 'Category2'
insert @t select 3, 1, 'Category3'
insert @t select 4 , 2, 'Category4'
insert @t select 5 , 1, 'Category5'
insert @t select 6 , 2, 'Category6'
insert @t select 7 , 3, 'Category7'
;
WITH tree (id, parentid, level, name, rn) as
(
SELECT id, parentid, 0 as level, name,
convert(varchar(max),right(row_number() over (order by id),10)) rn
FROM @t
WHERE parentid = 0
UNION ALL
SELECT c2.id, c2.parentid, tree.level + 1, c2.name,
rn
FROM @t c2
INNER JOIN tree ON tree.id = c2.parentid
)
SELECT *
FROM tree
order by RN
answered Jan 1 at 7:01
CodeManCodeMan
557311
answered Jan 1 at 7:01
CodeManCodeMan
557311
answered Jan 1 at 7:01
CodeManCodeMan
557311
answered Jan 1 at 7:01
CodeManCodeMan
557311
557311
you can use it in Trigger or ...
– CodeMan
Jan 1 at 7:03
by this way every id can have only one parent id. but in my database every id may have multiple parents.
– samen
Jan 1 at 9:01
I just copied my code for you, please customize it.
– CodeMan
Jan 1 at 11:03
add a comment |
you can use it in Trigger or ...
– CodeMan
Jan 1 at 7:03
by this way every id can have only one parent id. but in my database every id may have multiple parents.
– samen
Jan 1 at 9:01
I just copied my code for you, please customize it.
– CodeMan
Jan 1 at 11:03
you can use it in Trigger or ...
– CodeMan
Jan 1 at 7:03
you can use it in Trigger or ...
– CodeMan
Jan 1 at 7:03
by this way every id can have only one parent id. but in my database every id may have multiple parents.
– samen
Jan 1 at 9:01
by this way every id can have only one parent id. but in my database every id may have multiple parents.
– samen
Jan 1 at 9:01
I just copied my code for you, please customize it.
– CodeMan
Jan 1 at 11:03
I just copied my code for you, please customize it.
– CodeMan
Jan 1 at 11:03
add a comment |
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.
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%2f53993226%2fcategorize-records-of-table-in-sql%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
I don't follow your question, and I don't understand why
4444 -> 5555is in category 1. You might want to explain more about what you are doing here.– Tim Biegeleisen
Jan 1 at 5:49
because id=1 have relation with id=5 in 6th row. so both of them have same category. and id=4 have relation with id=5. so id=4 place in category=1 with id=4 and id=5
– samen
Jan 1 at 5:59
You might want to look into using a graph based database, such as GraphQL. But, tell us which version of SQL you are using (e.g. MySQL, SQL Server, Oracle, etc.).
– Tim Biegeleisen
Jan 1 at 6:00
Microsoft SQL server management studio 17
– samen
Jan 1 at 6:02
is it possible at all?
– samen
Jan 1 at 6:10