Hi,
I have been able to use Pivot previously - demonstrated by this simple example:
with sample_data as
(
select 6093064 id, 'display.attribute10.value' name, 'S' value_ from dual union all
select 6093064, 'display.attribute11.value', 'Y' from dual union all
select 6093064, 'display.attribute12.value', 'Y' from dual union all
select 6093064, 'display.attribute26.value', 'Receipt Accounting' from dual union all
select 6093073, 'display.attribute10.value', 'S' from dual union all
select 6093073, 'display.attribute11.value', 'Y' from dual union all
select 6093073, 'display.attribute12.value', 'Y' from dual union all
select 6093073, 'display.attribute26.value', 'Cost Management' from dual union all
select 6093363, 'display.attribute10.value', 'S' from dual union all
select 6093363, 'display.attribute11.value', 'Y' from dual union all
select 6093363, 'display.attribute12.value', 'Y' from dual union all
select 6093363, 'display.attribute26.value', 'Receipt Accounting' from dual union all
select 6093364, 'display.attribute10.value', 'S' from dual union all
select 6093364, 'display.attribute11.value', 'Y' from dual union all
select 6093364, 'display.attribute12.value', 'Y' from dual union all
select 6093364, 'display.attribute26.value', 'Cost Management' from dual
)
select * from (
select id
, name
, value_
from sample_data
)
pivot
(
max(value_)
for name in ('display.attribute10.value' report_style
, 'display.attribute11.value' transfertogeneralledger
, 'display.attribute12.value' postingeneralledger
, 'display.attribute26.value' application_or_ledger)
)
order by id desc
That returns this data:
ID________STYLE____GLTRX___GLPOST_______APP
6093364___S________Y_______Y____________Cost Management
6093363___S________Y_______Y____________Receipt Accounting
6093073___S________Y_______Y____________Cost Management
6093064___S________Y_______Y____________Receipt Accounting
However, I am struggling working out how to start with this sample data:
with sample_data as
(
select 1 id, 'Accounting Begin Balance' meaning, 6120421 request_id, NULL receivables_amt, 100 accounting_amt from dual union all
select 3, 'Receivables Begin Balance', 6120421,250, NULL from dual union all
select 11, 'Receivables End Balance', 6120421, 125, NULL from dual union all
select 18, 'Accounting End Balance', 6120421, NULL, 350 from dual
)
select id
, meaning
, request_id
, receivables_amt
, accounting_amt
from sample_data
ID_______MEANING________________REQUEST_ID RECEIVABLES_AMT ACCOUNTING_AMT
1 Accounting Begin Balance____6120421_____________________100
3 Receivables Begin Balance___6120421_____250
11 Receivables End Balance_____6120421_____125
18 Accounting End Balance______6120421_____________________650
And to possibly end up with a single row, with this type of output:
REQUEST_ID____Accounting Begin Balance______Accounting End Balance_______Receivables Begin Balance_____Receivables End Balance
6120421_______100___________________________350__________________________250___________________________125
Thanks for any advice provided, sorry for my mistakes and incompetencies!