Reformat ORACLE SQL Help

Hi experts,

I have written some complex SQL. What this code does is find a form submitted on the system, who submitted it, the question they were asked and the answer they gave.

I have attached an output of this code [capture.png]

I have now been asked to put each question [1,2,3,4,5 etc] across the top, and have just one entry for the employee.

So rather than having 6 entries for each employee, id like one entry and all the questions above the top.

IS this possible? Im not sure where to begin on it...

Thanks in advance,

Rich


 
with 
 
CTE_Questions as
(
  select * from
  (
    select 
    QQUES_REF, QQUES_ID, QQUES_TEXT, QQUES_LONG_DESC, QQUES_PUB_VERSION,
    row_number() over(partition by QQUES_ID order by QQUES_PUB_VERSION desc) as toprow 
    from 
    D1044M 
    where 
    QQUES_ID LIKE ('EF0047Q%')
  ) a
--  where 
  --toprow = 1
),
 
CTE_QuestDet as
(
  select * from
  (
    select
    QUES_REF, QUES_ID, QUES_SHORT_DESC, QUES_LONG_DESC, QUES_TITLE, QUES_PUB_VERSION,
    row_number() over(partition by QUES_ID order by QUES_PUB_VERSION desc) as toprow 
    from
    D1040M
    where
    QUES_ID in  ('EF0028')
  ) a
  --where toprow = 1
),  
 
CTE_Main as
(
  SELECT
  QuesFrmHdr.FORM_NUMBER,
  QuesFrmHdr.QINST_REF,
  CTE_Questions.QQUES_TEXT,
  qAnswers.QINST_VALUE,
  CTE_Questions.QQUES_ID
  
  FROM
  D1189M QuesFrmHdr
  inner join D1168M qAnswers on QuesFrmHdr.QINST_REF = qAnswers.QINST_REF
  inner join CTE_Questions on qAnswers.QINST_QQUES_REF = CTE_Questions.QQUES_REF
 
),
 
CTE_CurrAuth as
(
  select * from
  (
    SELECT
    AUTH_FORM_NUMBER,
    AUTH_KEY,
    AUTH_PERSON_REF,
    AUTH_ACTION_TYPE,
    AUTH_ACTION,
    AUTH_ACTION_DATE,
    ((((SUBSTR(auth.AUTH_ACTION_TIME,1 ,2) || ':') || SUBSTR(auth.AUTH_ACTION_TIME,3 ,2)) || ':') || SUBSTR(auth.AUTH_ACTION_TIME,5 ,2)) as AUTH_ACTION_TIME,
    NVL(AUTH_TYPE, 'SYSTEM') as AUTH_TYPE,
    row_number() over(partition by AUTH_FORM_NUMBER order by AUTH_ACTION_DATE desc, AUTH_ACTION_TIME desc, AUTH_ACTION asc) as toprow
 
    from
    D1521M auth
 
    where
    AUTH_EVENT_ID = 'WQUEST'
  ) a
  where a.toprow = 1
)
 
 
SELECT DISTINCT
e.EMPLOYEE_NUMBER,
per.KNOWN_AS as FORENAME,
per.SURNAME,
--e.ORIG_START_DATE,
--e.START_DATE,


CTE_Main.QQUES_TEXT as EFORM_QUESTION_TEXT,
CTE_Main.QINST_VALUE as ANSWER_TO_QUESTION,
--SUBSTR (CTE_Main.QQUES_ID, -1)as QUESTION_NUM,
--CTE_Main.QQUES_ID as QUESTION_NUM,
TO_NUMBER (SUBSTR(CTE_Main.QQUES_ID, 8))as QUESTION_NUM,
SUBSTR(qForms.QUES_SUBMITED_DATE, 0, 12) as DATE_SUBMITTED,

((((SUBSTR(qForms.QUES_SUBMITED_TIME,1 ,2) || ':') || SUBSTR(qForms.QUES_SUBMITED_TIME,3 ,2)) || ':') || SUBSTR(qForms.QUES_SUBMITED_TIME,5 ,2)) as TIME_SUBMITTED



FROM
D1169M qForms
inner join CTE_Main on qForms.QUES_FORM_NUMBER = CTE_Main.FORM_NUMBER
inner join D550M e on qForms.PERSON_REF = e.PERSON_REF
inner join D500M per on e.PERSON_REF = per.PERSON_REF
left join CTE_CurrAuth auth on CTE_Main.FORM_NUMBER = auth.AUTH_FORM_NUMBER
left join D800M authStat on authStat.NARRATIVE_CATEGORY = 'FORMST'
  and qForms.QUES_FORM_STATUS = authStat.NARRATIVE_CODE
where

QUES_FORM_STATUS ='AUTHED'and 

 trunc(QUES_SUBMITED_DATE) between trunc(sysdate)-1 and trunc(sysdate)



order by EMPLOYEE_NUMBER, QUESTION_NUM asc

 

Open in new window

Capture.PNG
Richiep86Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

johnsoneSenior Oracle DBACommented:
I'm not sure you can do that.  You have an unknown number of columns.  While you say there are 6 questions, from the picture you posted, it appears there can be multiple answers to the questions (especially 5 and 6).  What would you do in that case?
slightwv (䄆 Netminder) Commented:
I agree with johnsone:  You need to know the number of columns at parse time.

If there is a maximum number of columns then you have a chance.

Look at PIVOT (sorry, unable to locate a good doc link):
http://www.oracle.com/technetwork/articles/sql/11g-pivot-097235.html

If you want a comma separated list as a single column, look at LISTAGG:
http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions089.htm#CJABDFBD

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulEE Topic AdvisorCommented:
The data does show repeated answers by a user to a question. My assumption would be that you only report the most recent answer, and if that holds true then the image only shows  6 questions and that makes for a fixed number of columns.

For ignoring the older answers use
row_number() over(partition by employee_num, question_num
                                    order by date_submitted DESC, time_submitted DESC) as rn

and then filter for 1, e.g.

select
    employee_num
, max(case when rn=1 then answer_to_queston end) as q1
...
, max(case when rn=6 then answer_to_queston end) as q6
from (
           select *
           ,  row_number() over(partition by employee_num, question_num
                                                order by date_submitted DESC, time_submitted DESC) as rn
          from YourTable
           where ...
          )
where rn = 1
group by
    employee_num


If there are a variable number of questions to cater for (but not shown in the image) then you owuld probably need "dynamic sql" which is possible and can cater for variable column quantity.
johnsoneSenior Oracle DBACommented:
I don't think the assumption for only the newest answer is valid is true.  The questions have to do with your children.  I would think that you can have multiple children and all of them would be valid.
PortletPaulEE Topic AdvisorCommented:
Yes, good point. I wasn't reading the answer text too much.

But this just re-enforces the need for dynamic sql.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.