SQL - return multiple values in the same column

Question

Using SSMS, Table that looks like this

Agent	Location
A	1
A	2
B	3
B	4

---

How I run a query to get:

Agent	Location
A	1,2
B	3,4

Answer 1

You could try a Self Join on the Agent field like this:

SELECT 
  AGENT_A as AGENT, 
  CONCAT(CONCAT(LOCATION_A, ','), LOCATION_B) as LOCATION 
FROM (
  SELECT 
    A.AGENT as AGENT_A, 
    A.LOCATION as LOCATION_A, 
    B.AGENT as AGENT_B, 
    B.LOCATION as LOCATION_B 
  FROM SSMS as A
    LEFT JOIN SSMS as B
      on A.Agent = B.Agent) as T
WHERE LOCATION_A < LOCATION_B

Here you can see a Demo: https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=00cb9ecb7f0584c2436a0ee6bca6a30b

Answer 2

I use SQL Server 2019, but it also works for Azure sql db. So, if you want to return only distinct values, I'd suggest using rank() over() to discard any duplicated values from other agents. There is only one drawback, first unique location would be received by the first available agent.

In the code it is more clear to understand:

Create table Agents
(
    Agent char(1),
    Location int
)

insert into Agents
VALUES
('A', 1),
('A', 2),
('A', 6),
('B', 3),
('B', 4),
('C', 1),
('C', 4),
('C', 5)

select Agent, STRING_AGG([Location], ',') WITHIN GROUP (ORDER BY Location ASC) as Locations
from
(   
    select Agent, Location, rank() over (partition by [Location] order by Agent) as rnk
    from Agents
) as t
--will return agents with distinct locations, because they have the rank equals to 1
where t.rnk = 1
group by Agent

Here is link to test it: SQLize Online

Answer 3

A recursive CTE:

WITH cte1 as (
  SELECT 
     Agent, 
     CAST(Location AS VARCHAR(MAX)) Location,
     row_number() over (partition by Agent order by Location) R
  FROM SSMS
  ),
ctec as (
   SELECT Agent, count(*)  as c
   FROM SSMS
   GROUP BY Agent),
cte2 (Agent, Location, i, L) as (
   SELECT 
      Agent,
      CONCAT(Location,'') Location,
      1 as i ,
      Location L
   from cte1 
   where R=1
   
   union all
   
   select 
       cte2.Agent,
       CONCAT(cte2.Location, ',', cte1.Location),
       i+1,
       cte1.Location

   from cte1
   inner join cte2 on cte2.Agent=cte1.Agent 
                  and cte1.Location > cte2.Location  and cte1.R = i+1
   inner join ctec on cte2.Agent= ctec.Agent
   where  i < ctec.c
)

SELECT Agent,Location 
FROM cte2
WHERE i=(select c from ctec where ctec.Agent=cte2.Agent)
ORDER BY Agent;

see: DBFIDDLE

output, with some added data:

INSERT INTO SSMS VALUES ('C', '5');
INSERT INTO SSMS VALUES ('C', '6');
INSERT INTO SSMS VALUES ('C', '7');

INSERT INTO SSMS VALUES ('D', '5');
INSERT INTO SSMS VALUES ('D', '3');
INSERT INTO SSMS VALUES ('D', '1');
INSERT INTO SSMS VALUES ('D', '2');

Agent	Location
A	1,2
B	3,4
C	5,6,7
D	1,2,3,5