1

I have two columns with values

letter number
A      1
A      2
A      3
B      1
B      2

I want two aggregated, comma-separated, values representing the distinct set of the intersections of letter and number.

e.g

letters numbers
A,B     1,2
A       3
Emil
  • 249
  • 1
  • 2
  • 14
  • Have you tried a query yet which you can share with us? – Tim Biegeleisen Sep 13 '17 at 06:16
  • 2
    check your answer here-- https://stackoverflow.com/questions/12671117/comma-separated-values-with-sql-query – Rojelo Sep 13 '17 at 06:17
  • 2
    @Emil What result do you expect if your input contains 1 more row with C, 2? Or the first column has always only 2 distinct values? – sepupic Sep 13 '17 at 06:28
  • @sepupic I would expect to see: letters numbers A,B 1,2 A 3 C 2 – Emil Sep 13 '17 at 07:41
  • @akshaypatil I don't believe the linked answer suffices because it 'pivots' on the one column. I need to get the two comma-separated values from the intersection of the two columns. The accepted answer does this. – Emil Sep 13 '17 at 07:45

1 Answers1

2

What I have done is, first concatenated the letter column group by number column. Then given a row number partition by concatenated letters and order by number. Then again concatenated the number column group by the concatenated

Query

;with cte as(
    select *
    from (
        select [number], stuff((
                select ', ' + [letter] 
                from [your_table_name]
                where ([number] = t.[number]) 
                for xml path('')
            )
           , 1, 2, ''
        ) as letters
        from [your_table_name] t
        group by [number]
    )t2
)
select [letters], stuff((
        select ', ' + cast(number as varchar(100)) 
        from cte
        where ([letters] = t.[letters]) 
        for xml path('')
    )
    , 1, 2, ''
) as [numbers]
from cte t
group by [letters];

Find a demo here

Ullas
  • 11,450
  • 4
  • 33
  • 50