How to combine multiple rows into one, depening on an ID coming from a different table

Asked Feb 02 '23 at 10:25

Active Feb 02 '23 at 10:42

Viewed 33 times

I'm trying to add a number of values into one, depending on a value coming from multiple different tables. I keep getting a syntax error. So I have the following table:

OT_ID	CODE	OPTION	OPTION2
1	6T	S34	A1C
1	6T	Y78	Y08
1	6T	S34	A1C
2	7Y	Y78	YP8
2	7Y	S34	A1C
2	7Y	Y78	Y08

I want to combine the values in OPTION when the OT_ID is the same.

OT_ID	CODE	OPTION	OPTION2
1	6T	S34,Y78,S34	A1C,Y08,A1C
2	7Y	Y78,S34,Y78	TP8,A1C,Y08

I have tried to following query:

SELECT t1.ot_id, t0.code, t2.option, t3.option2
  FROM test t0
  LEFT JOIN test1 t1
    ON t1.ot_id = t0.id
  LEFT JOIN optn t2
    ON t1.ot_id = t2_id
  LEFT JOIN addtopn t3    
    ON t1.ot_id = t3.id

I tried STING_AGG, but I'm getting a syntax error.

edited Feb 02 '23 at 10:42

Barbaros Özhan

59,113
10
31
55

asked Feb 02 '23 at 10:25

Lvh

Which dbms are you using? – jarlh Feb 02 '23 at 10:27
Also show us your STRING_AGG attempt. – jarlh Feb 02 '23 at 10:27
I'm using oracle. I tried STRING_AGG(t2.OPTION, ',') As OPTION WITHIN GROUP (ORDER BY t1.ID) t2.OPTION – Lvh Feb 02 '23 at 10:32
2

Try LISTAGG instead. (STRING_AGG is for MS SQL Server.) https://docs.oracle.com/cd/E11882_01/server.112/e41084/functions089.htm#SQLRF30030 – jarlh Feb 02 '23 at 10:33

How to combine multiple rows into one, depening on an ID coming from a different table

0 Answers0