i have two tables

firstTable:

secondTable:

Expectation would be to get a view like this

Do you have an idea how i can achieve this with an statement in oracle? This is all i have till now...but i don't know how i can proceed... I thought i can work with Alias in the concatination..but i don't know how to use these to get the text through the different IDs...

select ID1text || ' ' || ID2text|| ' ' || ID3text||' '||
from firstTable join secondTable

Thank you in advance :)

CodePudding user response：

For small tables you may safe use a subquery to fetch the text value for eachIDand concatenate the result.

Example with Sample Data

with t1 as (
select 1 id, 1 id1, 2 id2, 3 id3 from dual union all
select 2,4,5,6 from dual),
t2 as (
select rownum id, 'Text'||rownum text from dual connect by level <= 6)
/* Your Query */
select 
 id,
 (select text from t2 where id = t1.id1)||' '||
 (select text from t2 where id = t1.id2)||' '|| 
 (select text from t2 where id = t1.id3) as  concTxt
from t1;

        ID CONCTXT                                                                                                                               
---------- --------------------- 
         1 Text1 Text2 Text3                                                                                                                     
         2 Text4 Text5 Text6 

Otherwise as commented use three joins, which produce the same result possible more performant for larger data.

with t1 as (
select 1 id, 1 id1, 2 id2, 3 id3 from dual union all
select 2,4,5,6 from dual),
t2 as (
select rownum id, 'Text'||rownum text from dual connect by level <= 6)
/* Your Query */
select 
 t1.id,
 t21.text ||' ' || t22.text || ' ' || t23.text as concTxt
from t1
join t2 t21 on t1.id1 = t21.id
join t2 t22 on t1.id2 = t22.id
join t2 t23 on t1.id3 = t23.id

CodePudding user response：

If you don't want to join the secondTable three times, you can use below alternative which is using Unpivoting clause to first unpivot the firstTable (columns ID* --> rows), and then joining it with the secondTable . All you have to do after that step is to group that result per "row" column, and use the Listagg aggregate function to concatenate texts as you need.

with firstTable_Unpivoted ( "Row", ID, source_column ) as (
    SELECT "Row", ID, source_column
    from firstTable
    unpivot (
        ID for source_column in (
            ID1 as 'ID1'
        ,   ID2 as 'ID2'
        ,   ID3 as 'ID3'
        )
    )
)
select 
    FTU."Row"
  , listagg(ST.Text, ' ') within group (order by FTU.ID) ConcatinatedText
from 
    firstTable_Unpivoted FTU
left join 
    secondTable ST
        on FTU.ID = ST.ID
group by FTU."Row"
;

T12:

SELECT   row_id
        ,LISTAGG( text, ',' ) WITHIN GROUP (ORDER BY id)
FROM     (WITH
              aa
              AS
                  (SELECT ids
                         ,row_id
                   FROM   t11 UNPIVOT (ids FOR id1 IN (id1, id2, id3)))
          SELECT *
          FROM   aa
                ,t12
          WHERE  aa.ids = t12.id)
GROUP BY row_id