CODEWITHSUNDEEP
sqloracle-database
Iratebeggar
Iratebeggar

Reputation: 13

Combine results into one

I'm trying to clean this up and only have one instance instead of 10.

This certain entry is connected to each of the individual data pieces within the CASE statements (10 of the 12 listed).

Seeing if anyone has any ideas (other than joining the CTE over and over, i.e.

LEFT JOIN sessions_CTE scte1 on scte1.person_id = p.person_id and 
scte1.session_id = '229856'
LEFT JOIN sessions_CTE scte2 on scte2.person_id = p.person_id and scte2.session_id = '229846'
LEFT JOIN sessions_CTE scte3 on scte3.person_id = p.person_id and scte3.session_id = '229798'...

That works fine, it just is huge and sketchy on the DB)

Here is my query, thanks for any ideas you guys might have:

WITH sessions_CTE AS
(
        select
            a.person_id,
            s.session_id,
            s.abbreviation,
            s.title,
            st.sessionTime_id,
            d.abbreviation as day,
            sl.minutes length,
            r.name room
          from
            attendeesregistered a
            inner join sessiontimes st on a.sessiontime_id = 
st.sessiontime_id
            inner join sessions s on st.session_id = s.session_id
            inner join slots on st.slot_id = slots.slot_id
            inner join days d on slots.day_id = d.day_id
            inner join rooms r on slots.room_id = r.room_id
            inner join slottimes on slots.slottime = 
slottimes.slottime_id
            inner join ses_length sl on slottimes.length = 
sl.ses_length_id
WHERE a.event = '23533'
--and ROWNUM <= 10
)

SELECT  DISTINCT p.person_id,
    p.email "Email",
    p.firstname "First Name",
    p.lastname "Last Name",
    p.jobTitle "Job Title",
    p.companyname "Company Name",
    CASE
        WHEN scte.session_id = '229744' THEN 'Registered'
       -- ELSE ''
    END as ExecutiveChannelPartnerMeeting,
    CASE
        WHEN scte.session_id = '229753' THEN 'Registered'
        ELSE ''
    END as ChickenParm,
    CASE
        WHEN scte.session_id = '229761' THEN 'Registered'
        ELSE ''
    END as Pesto,
    CASE
        WHEN scte.session_id = '229764' THEN 'Registered'
        ELSE ''
    END as mcyds,
    CASE
        WHEN scte.session_id = '229770' THEN 'Registered'
        ELSE ''
    END as bigmac,
    CASE
        WHEN scte.session_id = '229840' THEN 'Registered'
        ELSE ''
    END as mtndew,
    CASE
        WHEN scte.session_id = '229841' THEN 'Registered'
        ELSE ''
    END as sprite,
    CASE
        WHEN scte.session_id = '229852' THEN 'Registered'
        ELSE ''
    END as corndogs,
    CASE
        WHEN scte.session_id = '229853' THEN 'Registered'
        ELSE ''
    END as burritto,
    CASE
        WHEN scte.session_id = '229856' THEN 'Registered'
        ELSE ''
    END as mickeymouse,
    CASE
        WHEN scte.session_id = '229842' THEN 'Registered'
        ELSE ''
    END as mario,
    CASE
        WHEN scte.session_id = '229847' THEN 'Registered'
        ELSE ''
    END as link
    --scte.session_id,
    --scte.title
FROM people p
RIGHT JOIN sessions_CTE scte on scte.person_id = p.person_id
WHERE p.event = '23533'
AND p.person_id = 2388207
--AND ROWNUM <= 25
--GROUP BY p.person_id

Current output:

ID         |  Exec.   |Chicken Parm |  Pesto    | ......
----------- ---------- ------------- ----------- ------------
2388207    | Registered| (NULL)     | (NULL)    | ........
----------- ----------- ------------ ----------- ------------
2388207    | (NULL)    |  (NULL)    | Reistered | ........
----------- ----------- ------------ ----------- ------------
2388207    | (NULL)    |  Registered|  (NULL)   | ........
----------- ----------- ------------ ----------- ------------

Desired Output

ID         |  Exec.    |Chicken Parm |  Pesto    | ......
--------------------------------------------------------------
2388207    |Registered | Registered  | Registered| ........
----------- ----------- ------------ ----------- ------------

Upvotes: 1

Views: 61

Answers (1)

LukStorms
LukStorms

Reputation: 29677

You could group by, and then use MAX for the CASE WHEN's

WITH sessions_CTE AS
(
...
)
SELECT
p.person_id,
p.email AS "Email",
p.firstname AS "First Name",
p.lastname AS "Last Name",
p.jobTitle AS "Job Title",
p.companyname AS "Company Name",
MAX(CASE WHEN session_id = '229744' THEN 'Registered' END) AS "ExecutiveChannelPartnerMeeting",
MAX(CASE WHEN session_id = '229753' THEN 'Registered' END) AS "ChickenParm",
MAX(CASE WHEN session_id = '229761' THEN 'Registered' END) AS "Pesto",
MAX(CASE WHEN session_id = '229764' THEN 'Registered' END) AS "mcyds",
MAX(CASE WHEN session_id = '229770' THEN 'Registered' END) AS "bigmac",
MAX(CASE WHEN session_id = '229840' THEN 'Registered' END) AS "mtndew",
MAX(CASE WHEN session_id = '229841' THEN 'Registered' END) AS "sprite",
MAX(CASE WHEN session_id = '229852' THEN 'Registered' END) AS "corndogs",
MAX(CASE WHEN session_id = '229853' THEN 'Registered' END) AS "burritto",
MAX(CASE WHEN session_id = '229856' THEN 'Registered' END) AS "mickeymouse",
MAX(CASE WHEN session_id = '229842' THEN 'Registered' END) AS "mario",
MAX(CASE WHEN session_id = '229847' THEN 'Registered' END) AS "link"
FROM people p
RIGHT JOIN sessions_CTE scte on scte.person_id = p.person_id
WHERE p.event = '23533'
  AND p.person_id = 2388207
GROUP BY p.person_id, p.email, p.firstname, p.lastname, p.jobTitle, p.companyname;

Upvotes: 1

Related Questions