Oracle REGEXP_SUBSTR column names for PIVOT

Question

I am working with PL/SQL Developer v10 on Oracle 11g database.
In our system we have a question and answer table that I need to 'flatten' for each customer per question answered.

One set of questions had specific code (PIFQ) in description of question that made the following script possible. Simply UPPER(substr(q.questiondescription,1,6)) and then pivot on the list of possible codes.

 select * from (
                    select
                      tqs.transactiondetailid as transactiondetailid,
                      q.productid as productid,
                      tqs.answer as QAnswer,
                      UPPER(substr(q.questiondescription,1,6)) as QDesc,
                      tqs.transactionversion as transactionversion
                    from TRANSACTIONDETAILQSHIS tqs
                    inner join question q on q.questionid = tqs.questionid and 
                    q.questiondescription like 'PIFQ%'
    ) pivot (
    min(QAnswer) for QDesc in (
    'PIFQ01','PIFQ02','PIFQ03','PIFQ05','PIFQ06','PIFQ07','PIFQ08','PIFQ09','PIFQ10',
    'PIFQ11','PIFQ12','PIFQ13','PIFQ14','PIFQ15','PIFQ16','PIFQ17','PIFQ18','PIFQ19','PIFQ20',
    'PIFQ21','PIFQ22','PIFQ23','PIFQ24','PIFQ25','PIFQ26','PIFQ27','PIFQ28','PIFQ29','PIFQ30',
    'PIFQ31','PIFQ32','PIFQ33','PIFQ34','PIFQ35')
    )

which results in one line for all the questions answered in TRANSACTIONDETAILQSHIS.

now other set of questions has three codes (DT, WIF, WT) that are of different lengths.
some examples:

DT01. Are you married?
DT05. Do you have children?
WIF1.1.1 First Name
WIF1.2 Date Of Birth
WIF7.10 How many other properties do you own?
WIF14.3.7 Post Code
WIF15 Notes to solicitor
WT01. Will Type

None of codes have spaces in between and have space immediately after, but so do all the other codes in question table. I am thinking of using REGEXP_SUBSTR here to extract the codes for pivot and use same logic to capture QDesc. We are talking 100s of questions, so I would rather avoid listing the codes.

I got stuck with making regex (this is only the part that selects the correct question codes out of all, but after I am done with it - it will be a piece of cake). What I have ATM is:

select UPPER(REGEXP_SUBSTR(q.questiondescription,'(WIF|DT|WT)\d{1,2}.')) from question q

it does select the first group (WIF|DT|WT) and first numbers after

DT01. Are you married?
DT05. Do you have children?
WT01. Will Type

how do I do the logic that would capture the ones with . and without them at the end (WIF15).

WIF1.1.1 First Name
WIF1.2 Date Of Birth
WIF7.10 How many other properties do you own?
WIF14.3.7 Post Code
WIF15 Notes to solicitor

score 3 · Accepted Answer · answered Oct 14 '14 at 11:11

This will work '(WIF|DT|WT)[([:digit:]|.)]*'

SQLFiddle Demo

with my_data(str) as
(
select 'WIF1.1.1 First Name' from dual
union all
select 'WIF1.2 Date Of Birth' from dual
union all
select 'WIF7.10 How many other properties do you own?' from dual
union all
select 'WIF14.3.7 Post Code' from dual
union all
select 'WIF15 Notes to solicitor' from dual
)
select str, regexp_substr(str,'(WIF|DT|WT)[([:digit:]|.)]*') as result from my_data;

Result:

STR                                           RESULT
--------------------------------------------------------
WIF1.1.1 First Name                           WIF1.1.1
WIF1.2 Date Of Birth                          WIF1.2
WIF7.10 How many other properties do you own? WIF7.10
WIF14.3.7 Post Code                           WIF14.3.7
WIF15 Notes to solicitor                      WIF15

Skipped DT and WT questions, but it certainly does the job with `.` dots. — Matas Vaitkevicius, Oct 14 '14 at 11:15

vks · Answer 2 · 2014-10-14T11:16:31.537

0

(WIF|DT|WT)\d{1,2}.?

Try this.Make . optional.

edited Oct 14 '14 at 11:16

answered Oct 14 '14 at 11:08

vks

67,027
10
91
124

Oracle REGEXP_SUBSTR column names for PIVOT

2 Answers2