CODEWITHSUNDEEP
sqlsql-server
theo
theo

Reputation: 299

SQL Server Merging two records into one

I have this table:

--------------------------------
|ID|SN|SEC|Qty|DONE_CON|DONE_BY|
|--+--+---+---+--------+-------|
|1A|1A|ETC|124|YES     |       |
|2B|1A|CON|   |        |David  |
|1H|1H|ETC|764|YES     |       |
|2R|1H|CON|   |        |Maria  |
--------------------------------

What I want to do is to get all the CON as the SEC and show its ID, Qty and DONE_BY values.

My problem is Qty will come from its reference record (RF column).

Example:

CON record with ID 2B has 1A as its RF. So the Qty will be 124.

CON record with ID 2R has 1H as its RF. So the Qty will be 764.

Final Output should look like this:

----------------
|ID|Qty|DONE_BY|
|--+---+-------|
|2B|124|David  |
|2R|764|Maria  |
----------------

Currently, I only have this query:

SELECT ID, Qty, DONE_BY FROM TBL_TEST WHERE SEC='CON'

Please, help me. Thank you in advance.

Upvotes: 0

Views: 314

Answers (2)

Maverick Sachin
Maverick Sachin

Reputation: 883

Try this query --

SELECT T1.ID
    ,T2.Qty
    ,T1.DONE_BY
FROM tblSample T1
INNER JOIN tblSample T2 ON T1.SN = T2.ID
AND T1.Sec = N'CON'

Upvotes: 0

JNevill
JNevill

Reputation: 50019

I believe you are wanting a self join here so you can grab the qty from your matching SN->ID;

SELECT
    t1.id, 
    t2.qty as qty,
    t1.Done_By
FROM yourTable t1
    INNER JOIN yourTable t2 
        ON t1.sn = t2.id
WHERE t1.sec='con';

Upvotes: 1

Related Questions