Use distinct in string_agg function in SQL Server

200 views Asked by At

Let's assume that I have this table:

area    room
Area_1  Room1
Area_1  Room2
Area_1  Room3
Area_2  Room4
Area_2  Room5

I would like to get this result

areas            |  rooms
-----------------+---------------------------------------
Area_1 | Area_2  |  Room2 | Room3 | Room1 | Room5 | Room4

This is what I have done so far for this example:

select  
    STRING_AGG(area , ' | ') as areas,
    STRING_AGG(rooms, ' | ' ) as rooms
from (
    select area, STRING_AGG(room, ' | ') as rooms
    from 
    (
    select *
    from (
        values 
        ('Area_1','Room1'),
        ('Area_1','Room2'),
        ('Area_1','Room3'),
        ('Area_2','Room4'),
        ('Area_2','Room5')
        ) as a(area, room)
    ) example
    group by area
) c

In PostgreSQL it is possible to use the string_agg function with distinct, but in SQL Server this seems not to be available, reason why I had to use one subquery for grouping the area, and in the outer select finally get the expected result. I'm wondering if it is possible to have this output in one single select. In this example I have only two columns, but in the real case I have several columns that I need to group in the same way, and for each one of them I would need to use a subselect.

0

There are 0 answers