0

I have following three tables, from which I would like to get a specific result

TableA

key1    key2
-------------
121     4
131     4
141     5
151     3
161     3
171     6
181     6
191     6
...     ...

TableB:

key1    key3
-------------
121     1001
131     1111
141     1111
151     1222
161     1222
171     1234
181     1001
191     1111
...     ...

TableC:

key3    key4
-------------
1001    "aa"
1111    "gg"
1222    "hh"
1234    "jj"
...     ...

I want a SQL query (which could use inner join) to give me the following result:

key2    key4
-------------------------
3       "hh"
4       "aa", "gg"
5       "gg"
6       "aa", "gg", "jj"

Microsoft SQL Server 2012

ferhatelmas
  • 3,818
  • 1
  • 21
  • 25
vjjj
  • 1,019
  • 3
  • 10
  • 35

2 Answers2

1

You can use string_agg():

select t1.key2, string_agg(t3.key4, ',') key4
from table1 t1
inner join table2 t2 on t2.key1 = t1.key1
inner join table3 t3 on t3.key3 = t1.key3
group by t1.key2
GMB
  • 216,147
  • 25
  • 84
  • 135
  • error - 'string_agg' is not a recognized built-in function name – vjjj Mar 24 '20 at 20:46
  • @vjjj: are you sure that you are running this against a Posgres database? – GMB Mar 24 '20 at 20:59
  • Its SQL server, will change the tags, my bad! – vjjj Mar 24 '20 at 21:00
  • @vjjj: string aggregation functions are vendor-specific... Which version of SQL Server? 2017 has `string_agg()`. – GMB Mar 24 '20 at 21:02
  • Microsoft SQL Server 2012 – vjjj Mar 24 '20 at 21:04
  • @vjjj: in this version you need something like `for xml path`. You can have a look at [this question](https://stackoverflow.com/questions/15477743/listagg-in-sqlserver) for example. – GMB Mar 24 '20 at 21:10
0

Please try with below SQL query:

SELECT key2, string_agg(key4, ",")
FROM TableA JOIN TableB USING (key1) JOIN TableC USING (key3)
GROUP BY key2
Venkataraman R
  • 12,181
  • 2
  • 31
  • 58
ferhatelmas
  • 3,818
  • 1
  • 21
  • 25