CODEWITHSUNDEEP
sqlsql-server
AquaAlex
AquaAlex

Reputation: 380

Combining data from one table into a 2nd table

PROBLEM: I have two tables, Table1 with 3 columns[CODE], [CAT], [GROUP] and based on the values in Column 2 & 3 I want to create entries in Table2.

Table2 has 2 columns [CODE] and [ALTERNATECODE] - Also note Table2 has no data at start.

For every entry in Table1 (only if there is more than 1 matching entry) I want to create one entry in table2 for each matching case, with table2.CODE = table1.code, and Table2.alternatecode = Table1.code on matching entry(entries). MAtching is done on the table1.CAT and Table1.GROUP, all entries that match must have all other matching entries as ALTERNATECODES in Table2.

SQL:

DATA - TABLE1:

28644   138 10
27817   138 10
8019    138 11
8018    138 11
21044   138 12
20985   138 12
20986   138 12
10166   138 12
10165   138 12
8056    138 15
11611   139 1
21999   139 1
27497   139 1
11612   139 2
30245   139 3
84409   139 4
26023   139 4
22000   139 5

AFTER UPDATE - DATA TABLE2:

28644   27817
27817   28644
8019    8018
8018    8019
21044   20985
21044   20986
21044   10166
21044   10165
20985   21044
20985   20986
20985   10166
20985   10165
20986   21044
20986   20985
20986   10166
20986   10165
10166   21044
10166   20985
10166   20986
10166   10165
10165   21044
10165   20985
10165   20986
10165   10166
11611   21999
11611   27497
21999   11611
21999   27497
27497   21999
27497   11611
84409   26023
26023   84409

So basically the results for matching criteria. 1 entry in Table1 means 0 entries in table2, 2 entries in Table1 means we have 2 entries in table2 3 entries in Table1 means we have 6 entries in table2

Upvotes: 0

Views: 41

Answers (3)

sticky bit
sticky bit

Reputation: 37472

I hope I understood that correctly. Sounds like you want a self join on the category and group with unequal codes.

INSERT INTO table2
            (code,
             alternatecode)
            SELECT t11.code,
                   t12.code
                   FROM table1 t11
                        INNER JOIN table1 t12
                                   ON t12.cat = t11.cat
                                      AND t12.group = t11.group
                                      AND t12.code <> t11.code;

Upvotes: 2

Sean Lange
Sean Lange

Reputation: 33571

Not very clear what you are trying to do here. Maybe something like this?

select t1.Code
    , t1.Cat
from SomeTable t1
join SomeTable t2 on t1.Code = t2.Code 
    and t1.Group = t2.Group
    and t1.Cat <> t2.Cat

Upvotes: 1

EzLo
EzLo

Reputation: 14189

I believe you need a simple insert with direct and reversed relationships.

;WITH Relationships AS
(
    SELECT
        Code = T.Code,
        AlternateCode = N.Code
    FROM
        Table1 AS T
        INNER JOIN Table1 AS N ON 
            T.Cat = N.Cat AND
            T.[Group] = N.[Group]
    WHERE
        T.Code <> N.Code
)
INSERT INTO Table2 (
    Code,
    AlternateCode)
SELECT
    Code = D.Code,
    AlternateCode = D.AlternateCode
FROM
    Relationships AS D
UNION
SELECT
    Code = D.AlternateCode,
    AlternateCode = D.Code
FROM
    Relationships AS D

Upvotes: 1

Related Questions