convert rows into column in BIP datamodel

Raluca Balan-Oracle

Hello,

I have a datamodel with SQL where I tried to use unpivot function for BIP RTF table.

but below SQL is not working in order to achieve expected output. I mean I cannot even run it…

select line_num,unit_price, attribute_number1 from PO_LINES_DRAFT_ALL
unpivot (
splitl for years in (
line_num as 'column1',
unit_price as 'column2',
attribute_number1 as 'column3'
)
)
order by line_num
fetch first 6 rows only;

I would like to convert UNIT_PRICE, ATTRIBUTE_NUMBER1 rows values into columns as following

LINE_NUM	UNIT_PRICE	ATTRIBUTE_NUMBER1
1	1000000	0
2	1084000	8.4
3	1175056	8.4

Output should be:
	column1	column2	column3
CPI %	0	8.4	8.4
TOTAL	1000000	1084000	1175056

Can you please advise/help on this one?

Thank you!

Mallikarjuna Kuppauru-Oracle

Hi @Raluca Balan-Oracle

Are you able to run the same sql from sql developer and able to see the correct output?

https://blogs.oracle.com/sql/post/how-to-convert-rows-to-columns-and-back-again-with-sql-aka-pivot-and-unpivot#pivot

Regards,

Arjun