topic Re: tELTOracleXXXX in Talend Studio

tELTOracleXXXX

HanSolo44 — Fri, 01 Feb 2019 15:52:29 GMT

Hi,

I have one table (Oracle) under the template "header/lines" (with 1 to 5 lines max).

I want a new table where rows are replaced by columns in the same record. Is it possible ?

I tried to use tELTOracleXXXX components but I can't find the solution.

This is an example :

source table :

id_tab2	id_tab1	rate	amount	price
1		1		5		100		105
2		1		20		200		240
3		1		10		150		165

destination table :

id_tab	rate1	amount1	price1	rate2	amount2	price2	rate3	amount3	price3	rate4	amount4	price4	rate5	amount5	price5
1	5	100	105	20	200	240	10	150	165

Thank you for your help

Re: tELTOracleXXXX

akumar2301 — Thu, 28 Feb 2019 12:40:19 GMT

It is quite simple to do with PIVOT in Oracle itself. e.g.

SELECT *
FROM (SELECT id_tab1, id_tab2, price,amount
FROM tablename
where id_tab1=1)
PIVOT (SUM(price) AS price , SUM(amount) AS amount FOR (id_tab2) IN (1 AS a, 2 AS b, 3 AS c ,4 as d , 5 as e)
)
ORDER BY id_tab1;

Re: tELTOracleXXXX

HanSolo44 — Wed, 13 Mar 2019 13:29:24 GMT

Hi Abhishek,

Thank you for your response and sorry for my silence.

I saw this solution but my problem was that the lines were not with identifiers (numbers) and limited to 5.
I used a job to create a table with the same description and a new column to classed this rows. After, with the tELTOracleMap component, I am able to make join on this rows with this new column and left outer join.

Best regards.

Philippe