PIVOT or PIVOT XML without listing the columns explicitly on Oracle

Question

I am working on PL/SQL Developer v10 on Oracle 11g database. The idea is instead of writing 145 columns listed in PIVOT statement write an Pivot XML and get data from there, I am working on a basis of this answer, as I have never done PIVOT XML before on Oracle so my expectations might be wrong

select * from (
                    select
                      tqs.transactiondetailid as transactiondetailid,
                      q.productid as productid,
                      tqs.answer as QAnswer,
                      regexp_substr(q.questiondescription,'(WIF|DT|WT)[([:digit:]|.)]*') as QDesc,
                      tqs.transactionversion as transactionversion
                    from TRANSACTIONDETAILQS tqs
                    inner join question q on q.questionid = tqs.questionid and (
                          q.questiondescription like 'DT%' 
                          or  q.questiondescription like 'WT%' 
                          or q.questiondescription like 'WIF%')
    ) pivot xml(
    min(QAnswer) for QDesc in (
    select regexp_substr(q.questiondescription,'(WIF|DT|WT)[([:digit:]|.)]*') as r from question q
    where regexp_substr(q.questiondescription,'(WIF|DT|WT)[([:digit:]|.)]*') IS NOT NULL)
    )

in XML column does not return any data they all are empty, however following statement where I list columns explicitly works just fine

select * from (
                    select
                      tqs.transactiondetailid as transactiondetailid,
                      q.productid as productid,
                      tqs.answer as QAnswer,
                      regexp_substr(q.questiondescription,'(WIF|DT|WT)[([:digit:]|.)]*') as QDesc,
                      tqs.transactionversion as transactionversion
                    from TRANSACTIONDETAILQS tqs
                    inner join question q on q.questionid = tqs.questionid and (
                          q.questiondescription like 'DT%' 
                          or  q.questiondescription like 'WT%' 
                          or q.questiondescription like 'WIF%')
    ) pivot (
    min(QAnswer) for QDesc in (
    'DT01.','DT02.','WT01.')
    )

How to pivot without listing the columns explicitly?

not certain, but your `regexp_substr(q.questiondescription,'(WIF|DT|WT)...` may be wrong. What happens if you reduce the complexity, and try to output just one column name as match, before trying to get all of them? Good luck. — shellter, Oct 15 '14 at 03:38
@shellter 9 months later I figured out where the problem lies, see update. :) — Matas Vaitkevicius, May 19 '15 at 10:11

score 2 · Accepted Answer · answered May 19 '15 at 10:49

I boiled it down to the problem of

min(QAnswer) for QDesc In (
    select 'DT01.','DT02.','WT01.' from dual )
)

not working, but

min(QAnswer) for QDesc In (
    'DT01.','DT02.','WT01.')
)

working fine.

One way to do this is create dynamic PL/SQL but I don't have enough courage to do this.

PIVOT or PIVOT XML without listing the columns explicitly on Oracle

1 Answers1