CODEWITHSUNDEEP
sqljoin
Blake Lassiter
Blake Lassiter

Reputation: 383

Combine rows in SQL On Join

I want to combine pairs of rows into two columns. Currently, I am able to combine them into two columns, but I want to remove the instances where if the left column is 'A', then 'A' does not also appear in the right side. Essentially I have double the rows that I should.

Also, I read about using pivot, but could not get that to work. 

Select t1.ID, concat(t1.Notes,' ' + t2.Notes) as Notes, t1.Flag, t2.ID as Old_ID
From mytable as t1
Left outer Join mytable as t2
    On ( t1.Flag = t2.Flag and t1.ID != t2.ID)
where t1.Notes is not null

Data:

ID |   NOTES | Flag
A       YES     1
B       YES     2
C       YES     3
D       YES     4

Current Output: 

ID |   NOTES   | Flag | Old_ID
A     YES YES      1     B
C     YES YES      3     D
B     YES YES      2     A
D     YES YES      4     C

Expected Output: 

ID |   NOTES   | Flag | Old_ID
A     YES YES     1      C
B     YES YES     2      D

Upvotes: 1

Views: 58

Answers (2)

Nate
Nate

Reputation: 660

Add to your where clause

WHERE
    t1.Id < t2.Id

Upvotes: 3

Tony Chiboucas
Tony Chiboucas

Reputation: 5683

There's no way to determine which is the 'OLD' entry

A     YES YES      1     B  - which takes precidence?
B     YES YES      2     A

You must define some critera by which to prioritize one entry over the other.

SELECT
    t1.ID,
    concat(t1.Notes,' ' + t2.Notes) AS Notes,
    t1.Flag,
    t2.ID AS Old_ID
FROM mytable AS t1
LEFT OUTER JOIN mytable AS t2
    ON ( t1.Flag = t2.Flag AND t1.ID != t2.ID)
WHERE
    t1.Notes IS NOT NULL
    AND t1.id NOT IN (
        SELECT MAX(id) FROM mytable GROUP BY Flag
    )

Upvotes: 0

Related Questions