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:
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
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.