1

I have following three tables, from which I would like to ge 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 :-

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

I got an answer from Stackoverflow with the following query :-

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

But my SQL server does not allow string_agg() function!

How can I replace string_agg() with its equivalent form for older version of SQL server?

SQL - Microsoft SQL Server 2012

vjjj
  • 1,019
  • 3
  • 10
  • 35

1 Answers1

0

For the uninitiated, grouped concatenation is when you want to take multiple rows of data and compress them into a single string (usually with delimiters like commas, tabs, or spaces). Some might call this a "horizontal join." There have been many ways to solve this problem over the years:

Scalar UDF, Common Language Runtime (CLR), Recursive CTE, Cursor

Have a read here: https://sqlperformance.com/2014/08/t-sql-queries/sql-server-grouped-concatenation

Also have a read here:

How to make a query with group_concat in sql server

t1f
  • 3,021
  • 3
  • 31
  • 61