0

I have the following sql:

select distinct a1.Id, a1.Value from
#table1 a1
where a1.Id in
(select a2.Id from #table1 a2
join #table1 a3 on a2.Id = a3.Id
 where a2.Value <> a3.Value)

It groups the same Ids from the same table, in case their respective values are different. Example output:

| Id |Value|
| 1  |  1  |
| 1  |  3  |
| 1  |  2  | 
| 2  |  4  | 
| 2  |  5  |

I want to concatenate this rows into one so it looks like this:

| Id | Value | 
| 1  | 1,3,2 |
| 2  | 4,5   |

I tried using for xml path, but I can't figure out how to do it correctly: 

select a1.Id, 
SUBSTRING(
(
SELECT ',' + CAST(a1.Value as varchar(50)) [text()]
where a1.Id in
    (select a2.Id from #table1 a2
    join #table1 a3 on a2.Id= a3.Id
    where a2.Value<> a3.Value
    ) 
FOR XML PATH ('') 
), 2, 1000) [Value]
FROM #table a1

But it is not working. I am using Microsoft SQL. I have a problem of transforming 'in sub-query' into valid sql where I could use 'FOR XML PATH('')'. I tried using cross apply, but I can't figure out how to correctly join 3 times the same table

Shukhrat Raimov
  • 2,137
  • 4
  • 21
  • 27

0 Answers0