0

I have a table that contains a bunch of keys with different IDs. Some keys have more than 1 ID.

ID Table:

key      id
 a        1
 a        11
 a        12
 b        2
 c        3
 c        33

2nd table:

key     count         location
 a        17         123 Test Rd
 b        10         12 Smith St
 c        18         999 Fire Rd

Desired result:

key     count         location        id
 a        17         123 Test Rd     1, 11, 12
 b        10         12 Smith St      2
 c        18         999 Fire Rd      3, 33

I am trying to join this table with another table that I have so that the ID is carried over, but the issue I am running in to is that my join result is getting a larger number of rows than desired simply because it is creating a row for each unique ID that a key has. Is there a way to do the JOIN in a way where it groups each id together (perhaps comma delimited) in one single row?

M. Pollino
  • 45
  • 6
  • 1
    Show us some sample data for the other table too, as well as the expected result. – jarlh Feb 02 '23 at 14:06
  • *"perhaps comma delimited"* sounds like string aggregation is what you want. There are (literally) 100's, if not 1,000's, of answers demonstrating how to do that. – Thom A Feb 02 '23 at 14:09
  • 1
    @jarlh I added in the data I have. It is literally dummy data. – M. Pollino Feb 02 '23 at 14:09

1 Answers1

1

You can use STRING_AGG:

SELECT t.key, s.count, s.location, STRING_AGG(t.id, ',') as id
FROM YourTable t
JOIN YourTable2 s
 ON(t.key = s.key)
GROUP BY t.key, s.count, s.location
sagi
  • 40,026
  • 6
  • 59
  • 84