CODEWITHSUNDEEP
sqloracle-database
sandsawks
sandsawks

Reputation: 309

Oracle SQL Aggregate Multiple Rows to Single Row

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>

Upvotes: 0

Views: 129

Answers (1)

Jon Ekiz
Jon Ekiz

Reputation: 1022

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

Upvotes: 2

Related Questions