Blake Lassiter Blake Lassiter - 5 months ago 9
SQL Question

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

Answer

Add to your where clause

WHERE
    t1.Id < t2.Id