Create comma seperated list for joined table in Oracle

Question

i have two table 'ga_mise_en_stage' alias 'm'

and 'ga_annee_academique' alias 'ac', and

on 'ga_annee_academique' there is foreign key that point to 'ga_mise_en_stage'.

It is difficult for me to make query to retrive m.code_mise_stage, m.Libelle and all ac.annee_academique that related to m.code_mise_stage in the same ligne

schema for ga_mise_en_stage : code_mise_enstage(pk), libelle

schema for ga_annee_academique : code_annee_academique(pk), annee_academique, code_mise_en_stage(fk)

I want comma seperated result for m.*, "ac.annee_academique, ..."

Can you also provide your current query? Some of us find the question as stated a little confusing. — Clockwork-Muse, Mar 18 '14 at 09:55
U should give us the definition of the 2 tables with the fields noumns... — ASNAOUI Ayoub, Mar 18 '14 at 09:56

Shantanu Gupta · Answer 1 · 2014-03-18T10:14:28.233

1

You need to join on your foreign key..

select m.code_mise_stage, m.Libelle, ac.annee_academique, ac.*
FROM ga_annee_academique ac
inner join ga_mise_en_stage m
on ac.code_mise_en_stage = m.code_mise_enstage

EDIT: Comma seperated list - This works only on SQL Server

SELECT  m.code_mise_stage, m.Libelle, STUFF((SELECT  ',' + ac.annee_academique
            FROM ga_annee_academique ac
            WHERE  m.code_mise_enstage=ac.code_mise_en_stage
        FOR XML PATH('')), 1, 1, '') AS listStr
FROM ga_mise_en_stage m
GROUP BY m.code_mise_stage, m.Libelle

EDIT: Using system table to make this example more generic.

select t.TABLE_NAME, STUFF
(
    (select ', ' + c.COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS c 
        WHERE c.TABLE_NAME = t.TABLE_NAME FOR XML PATH(''))
    , 1,1, ''
)
from INFORMATION_SCHEMA.TABLES t where TABLE_NAME = 'ga_mise_en_stage'

Here is a documentation on XML PATH

In this example, query below returns XML as a string where each element is seperated by ',' which is used as an XML element.

select ', ' + c.COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS c 
        WHERE c.TABLE_NAME = 'FormulaMetrics_tbl' FOR XML PATH('')

edited Mar 18 '14 at 10:14

answered Mar 18 '14 at 09:58

Shantanu Gupta

20,688
54
182
286

thanks Mr Shantanu, but iwant that 'ac.annee_academique' that related to specific 'm.code_mise_stage' be concatained – ucef Mar 18 '14 at 10:01
i test your last answers, there is missing right parathense that i don't find it, can you explain please 'for xml path' – ucef Mar 18 '14 at 10:08
This works only on sql server – Shantanu Gupta Mar 18 '14 at 10:17
oh no, i use oracle :/, but thank you Mr Gupta. – ucef Mar 18 '14 at 10:19
You can search for comma seperated list in Oracle. check if this works for you http://stackoverflow.com/questions/468990/how-can-i-combine-multiple-rows-into-a-comma-delimited-list-in-oracle Updated your question to make it more clear. – Shantanu Gupta Mar 18 '14 at 10:22
Thanks Mr Shantanu, it's done ! – ucef Mar 18 '14 at 10:55

score 0 · Answer 2 · answered Mar 18 '14 at 10:53

the Query will be like that :

 select M.LIBELLE_STAGE, M.CODE_MISE_STAGE, JOINATE.anneeAcademique from ga_mise_en_stage m
 inner join (select AC.CODE_MISE_EN_STAGE, listagg(AC.ANNEE_ACADEMIQUE, ',') within group (order by AC.CODE_MISE_EN_STAGE) as anneeAcademique from ga_annee_academique ac
  group by AC.CODE_MISE_EN_STAGE) joinate 
  on JOINATE.CODE_MISE_EN_STAGE = M.CODE_MISE_STAGE

Create comma seperated list for joined table in Oracle

2 Answers2