SQL Logic
-
I'm trying to figure how to write an SQL Statement against TABLE #1 & create a table that looks like TABLE#2 - I would like TABLE#2 to only have DISTINCT VALUES in Column#1 & a FLAG in Column two that shows if the values are related or not.
TABLE#1 has two columns and each row has relationships: ex. row 1 shows that value A relates to B & in row two: C relates to A, & so on, therefore I want to create TABLE#2 that flags these relationships. Values D, E, F are tied so they get FLagged with a 2, Values A,B,C, & G get flagged with a 1 & so on, if A had a relationship with value Z - I would want to Flag value Z in TABLE#2 with a 1. etc.
TABLE#1 has millions of rows and one Value can have many relationsips to other values - however each row is unique - TABLE#1 doesn't have A & B appearing in another row together, although it may appear as B & A, where the value B is in Column#1 & value B is in Column#2. I have been reading about WHILE & FETCH statements however I cannot visualize the logic and I'm not experienced in writing SQL queries with LOOPs, I'm hoping someone can assist me, thanks.
TABLE#1 TABLE#2
Column#1 Columns#2 Column#1 Column#2
A B A 1
C A B 1
D F C 1
B C D 2
F E E 2
C G F 2
G 1
-
Sorry, but your question is extremely vague. Can you post the create scripts for the "table #1" and more detail on what you want the output to look like.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. Selburg -
Thanks, yes, I updated my post, I hit the submit button in error....
-
If you post the Create scripts and some sample data along with what you expect, I'm sure someone on this site could help you a bit more.
It's rather difficult to get exactly what your looking for from your post.
FYI: To get the create scripts, right click the table in Management Studio and select Script Table AS Create to new query window.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. Selburg -
Hopefully this will help: I'm looking to create an output like TABLE#2. I already flagged GROUPS A, B, C & G in TABLE#2 as "1" and GROUPS D, E, F as "2", the GROUPS can switch between BUYER & SELLER in TABLE#1. Even though there is no row with GROUPS G & A together they are still flagged with a "1" because there is an interactioon with GROUPs C & G, C & A, therefore one GROUP gets linked to the other. As you can see if one GROUP buys or sells to many other GROUPs the link could get very large because TABLE#1 has millions of rows. If there was a GROUP Z in my example and they ony dealt with GROUP T then I want to flag Z & T with a "3". As you can see in the TABLE#2 output file below there should only be DISTINCT GROUPS however the FLAG would not be DISTINCT, thanks.
CREATE
TABLE [dbo].[TABLE_1](
[BUYER_GROUP] [char]
(4) NOT NULL,
[SELLER_GROUP] [char]
(4) NOT NULL)
insert
into TABLE_1 VALUES ('A','B')
insert
into TABLE_1 VALUES ('C','A')
insert
into TABLE_1 VALUES ('D','F')
insert
into TABLE_1 VALUES ('B','C')
insert
into TABLE_1 VALUES ('F','E')
insert
into TABLE_1 VALUES ('C','G')
SELECT * FROM TABLE_1
BUYER_GROUP SELLER_GROUP
-------------- -------------
A B
C A
D F
B C
F E
C G
GO
CREATE
TABLE [dbo].[TABLE_2](
[DISTINCT_GROUPS] [char]
(4) NOT NULL,
[FLAG] [int]
NULL
)
ON [PRIMARY]
GO
insert
into TABLE_2 VALUES ('A','1')
insert
into TABLE_2 VALUES ('B','1')
insert
into TABLE_2 VALUES ('C','1')
insert
into TABLE_2 VALUES ('D','2')
insert
into TABLE_2 VALUES ('E','2')
insert
into TABLE_2 VALUES ('F','2')
insert
into TABLE_2 VALUES ('G','1')
SELECT * FROM TABLE_2
DISTINCT_GROUP FLAG
-------------- -----------
A 1
B 1
C 1
D 2
E 2
F 2
G 1
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply