CODEWITHSUNDEEP
sql-server
Emil
Emil

Reputation: 349

Convert intersection of two columns into two comma-separated values

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

Upvotes: 1

Views: 105

Answers (1)

Ullas
Ullas

Reputation: 11556

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

Upvotes: 2

Related Questions