September 20, 2012 at 6:42 am
Hi
I have a table
C1 C2
10 20
10 30
30 20
40 50
20 60
50 70
80 10
70 90
and i would like a list of the C1 and C2 fields that relate to each other and then an id for the related fields
RelatedCs ID
10 1
20 1
30 1
60 1
80 1
40 2
50 2
70 2
90 2
does this make sense?
thanks
September 20, 2012 at 6:54 am
It's not clear (at least for me). What logic do you use to say a field is a related field?.
You should take a look at the link in my signature to get better help.
September 20, 2012 at 7:02 am
if you look at the RelatedCs with the Related ID of 1
RelatedCs ID
10 1
20 1
30 1
60 1
80 1
you can tell they are related by looking at the following rows in the original table
C1 C2
10 20
10 30
30 20
20 60
80 10
and the following rows down fall into related ID 1 as none of the Cs in the list relate to any of the Cs in the raleted ID 1 sequence and therefore become related ID 2
C1 C2
40 50
50 70
70 90
if i was to add one more row in the original table
C1 C2
40 30
then all of the Cs would be related
is that clearer?
September 20, 2012 at 7:30 am
try this: http://www.sqlservercentral.com/articles/Best+Practices/61537/
it should help you to get a prompt help.
September 20, 2012 at 7:35 am
Hi
Sorry for the bad post
heres the original table
create table #tab
(c1 int, c2 int)
insert into #tab values (10, 20)
insert into #tab values (10, 30)
insert into #tab values (30, 20)
insert into #tab values (40, 50)
insert into #tab values (20, 60)
insert into #tab values (50, 70)
insert into #tab values (80, 10)
insert into #tab values (70, 90)
September 20, 2012 at 7:44 am
I might be doing some extra work with this query and someone could find a better way to do it.
However, it's an option that you can analyze.
DECLARE @test-2TABLE(
C1 int, C2 int)
INSERT @test-2 VALUES
(10, 20),
(10, 30),
(30, 20),
(40, 50),
(20, 60),
(50, 70),
(80, 10),
(70, 90);
WITH Original AS( --Order the original values to have the lowest always as C1
SELECT C1, C2
FROM @test-2
WHERE C1 < C2
UNION
SELECT C2, C1
FROM @test-2
WHERE C2 < C1),
Related AS( --Establish all the related Cs to the lowest value.
SELECT o1.C1 AS parent,
o1.C1, o1.C2
FROM Original o1
WHERE C1 NOT IN( SELECT C2 FROM Original)
UNION ALL
SELECT r.parent,
o.C1, o.C2
FROM Related r
JOIN Original o ON r.C2 = o.C1),
Final AS( --Have all Cs in a single column associated to their "root"
SELECT parent, C1
FROM Related
UNION
SELECT parent, C2
FROM Related)
SELECT DENSE_RANK() OVER( ORDER BY Parent) AS id,
C1 C
FROM Final
September 20, 2012 at 8:17 am
This looks a lot like your other question. http://www.sqlservercentral.com/Forums/Topic1361875-391-1.aspx
On neither of these of posts have you provided anywhere near enough detail for anybody to do much but try to decipher your chicken scratch and guess at what you are trying to do. The reason you are not getting answers is because you are not providing the whole question.
http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 20, 2012 at 8:21 am
thanks a lot mate
it works a treat for the example table
issue i have now is that for an 800 row table it takes ages
September 20, 2012 at 8:22 am
well spotted pal, the two are linked
completely different problem tho
September 20, 2012 at 8:30 am
erics44 (9/20/2012)
well spotted pal, the two are linkedcompletely different problem tho
But same issue of lack of details. I guess Luis has his mind reading cap on today.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 20, 2012 at 8:35 am
Sean Lange (9/20/2012)
erics44 (9/20/2012)
well spotted pal, the two are linkedcompletely different problem tho
But same issue of lack of details. I guess Luis has his mind reading cap on today.
you ok matey? got out of the wrong side of the bed?
the other question was answered by multiple people conclusively, perhaps the world is full of mind readers do you think?
September 20, 2012 at 8:42 am
erics44 (9/20/2012)
Sean Lange (9/20/2012)
erics44 (9/20/2012)
well spotted pal, the two are linkedcompletely different problem tho
But same issue of lack of details. I guess Luis has his mind reading cap on today.
you ok matey? got out of the wrong side of the bed?
the other question was answered by multiple people conclusively, perhaps the world is full of mind readers do you think?
Perhaps my response was more aggressive than it needed to be. However in both of your threads the people that helped you had to post something usable first. The point I was trying to make is that if you do a little of the leg work the people around here (all volunteers) can spend their time working on your issue instead of setting it up. You will have a much wider audience if you put in the effort so they don't have to. Hopefully in the future you will post ddl and sample data so other don't have to create it for you.
Glad you were able to fix your issues.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 20, 2012 at 8:46 am
For a small improvement, change the "Original" definition:
Original AS(
SELECT CASE WHEN C1 < C2 THEN C1 ELSE c2 END AS C1,
CASE WHEN C1 < C2 THEN C2 ELSE C1 END AS C2
FROM @test-2),
Mind reading? maybe, this happens after the second cup of coffee 😀
September 20, 2012 at 8:51 am
Sean Lange (9/20/2012)
erics44 (9/20/2012)
Sean Lange (9/20/2012)
erics44 (9/20/2012)
well spotted pal, the two are linkedcompletely different problem tho
But same issue of lack of details. I guess Luis has his mind reading cap on today.
you ok matey? got out of the wrong side of the bed?
the other question was answered by multiple people conclusively, perhaps the world is full of mind readers do you think?
Perhaps my response was more aggressive than it needed to be. However in both of your threads the people that helped you had to post something usable first. The point I was trying to make is that if you do a little of the leg work the people around here (all volunteers) can spend their time working on your issue instead of setting it up. You will have a much wider audience if you put in the effort so they don't have to. Hopefully in the future you will post ddl and sample data so other don't have to create it for you.
Glad you were able to fix your issues.
this was a point already made and resolved in this very thread
it didnt need any additional involvement from the sql server central police 🙂
shouldnt you be out on the street checking if anyone else id posting properly?
September 20, 2012 at 8:56 am
erics44 (9/20/2012)
Sean Lange (9/20/2012)
erics44 (9/20/2012)
Sean Lange (9/20/2012)
erics44 (9/20/2012)
well spotted pal, the two are linkedcompletely different problem tho
But same issue of lack of details. I guess Luis has his mind reading cap on today.
you ok matey? got out of the wrong side of the bed?
the other question was answered by multiple people conclusively, perhaps the world is full of mind readers do you think?
Perhaps my response was more aggressive than it needed to be. However in both of your threads the people that helped you had to post something usable first. The point I was trying to make is that if you do a little of the leg work the people around here (all volunteers) can spend their time working on your issue instead of setting it up. You will have a much wider audience if you put in the effort so they don't have to. Hopefully in the future you will post ddl and sample data so other don't have to create it for you.
Glad you were able to fix your issues.
this was a point already made and resolved in this very thread
it didnt need any additional involvement from the sql server central police 🙂
shouldnt you be out on the street checking if anyone else id posting properly?
I apologized for being aggressive. A little smiley face does not the mask the tone of your snarky response.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 15 posts - 1 through 15 (of 44 total)
You must be logged in to reply to this topic. Login to reply