How do I perform unique self join with intersection on a table

You can use an inequality condition in the join to get the right raw data:

select a2.col1, a1.col1, a1.col2
from TableA a1
join TableA a2 on a1.col2 = a2.col2 and a1.col1 < a2.col1
order by a2.col1, a1.col1, a1.col2

See it work here:

http://sqlfiddle.com/#!18/4914d/2

This gives the correct results without functions, but does not combine the two col1 values into one comma-separated field. For that part, while some databases allow an implicit conversion, Sql Server will require a CAST() or CONVERT() function to turn the numeric (int) data into text, and there’s no way around it. However, from a database perspective, this is work that is better done in client code anyway.

CLICK HERE to find out more related problems solutions.

Leave a Comment

Your email address will not be published.

Scroll to Top