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.