Oracle SQL Aggregate Multiple Rows to Single Row

Question

I have a table that stores employee punches in a 1 row per PUNCHDTM format. The table has a SHIFTID to correlate all the punches that belong to their shift and I want a way to get all the individual punches in their own column giving me a 1 row per SHIFTID. I can't determine how many punches there are (some have 2 some have 10,12 ??) which makes me think something dynamic would be difficult. If it's not possible to dynamically get all the punches then just grabbing the first 6 punches would likely suffice.

Here is an example dataset (I ranked the punches per shift with DENSE_RANK)

<table border="1">
<tr><th>EMPLOYEEID </th><th> SHIFTID </th><th> SHIFTDATE  </th><th> PUNCHDTM            </th><th> RANK</th></tr>
<tr><td>0000016431 </td><td> 1400510 </td><td> 10/16/2017 </td><td> 10/16/2017 03:29 pm </td><td> 1</td></tr>
<tr><td>0000016431 </td><td> 1400510 </td><td> 10/16/2017 </td><td> 10/16/2017 08:02 pm </td><td> 2</td></tr>
<tr><td>0000016431 </td><td> 1400510 </td><td> 10/16/2017 </td><td> 10/16/2017 08:32 pm </td><td> 3</td></tr>
<tr><td>0000016431 </td><td> 1400510 </td><td> 10/16/2017 </td><td> 10/16/2017 10:30 pm </td><td> 4</td></tr>
<tr><td>0000016431 </td><td> 1404581 </td><td> 10/17/2017 </td><td> 10/17/2017 02:57 pm </td><td> 1</td></tr>
<tr><td>0000016431 </td><td> 1404581 </td><td> 10/17/2017 </td><td> 10/17/2017 07:20 pm </td><td> 2</td></tr>
</table>

And desired result set

<table border="1">
<tr><th>EMPLOYEEID </th><th> SHIFTID </th><th> SHIFTDATE  </th><th> PUNCHDTM1           </th><th> PUNCHDTM2           </th><th> PUNCHDTM3           </th><th> PUNCHDTM4</th></tr>
<tr><td>0000016431 </td><td> 1400510 </td><td> 10/16/2017 </td><td> 10/16/2017 03:29 pm </td><td> 10/16/2017 08:02 pm </td><td> 10/16/2017 08:32 pm </td><td> 10/16/2017 10:30 pm</td></tr>
<tr><td>0000016431 </td><td> 1404581 </td><td> 10/17/2017 </td><td> 10/17/2017 02:57 pm </td><td> 10/17/2017 07:20 pm </td><td> NULL                </td><td> NULL</td></tr>
</table>

Jon Ekiz · Accepted Answer · 2018-08-31 20:23:48Z

2

Like you said, it's hard to do this dynamically coz it's going to require hardcoding. but you can definitely do it for 6 punches:

select EMPLOYEEID
    , SHIFTID
    , SHIFTDATE
    , max(case when rank = 1 then PUNCHDTM else null end)  as PUNCHDTM1
    , max(case when rank = 2 then PUNCHDTM else null end)  as PUNCHDTM2
    , max(case when rank = 3 then PUNCHDTM else null end)  as PUNCHDTM3
    , max(case when rank = 4 then PUNCHDTM else null end)  as PUNCHDTM4
    , max(case when rank = 5 then PUNCHDTM else null end)  as PUNCHDTM5
    , max(case when rank = 6 then PUNCHDTM else null end)  as PUNCHDTM6
from employees
group by 1,2,3

answered Aug 31, 2018 at 20:23

Jon Ekiz

1,0327 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Oracle SQL Aggregate Multiple Rows to Single Row

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related