SQL combine and join row data with same Table

Question

My SQL Query

SELECT emp_location, emp_system_name, emp_compName, tran_type_code, emp_morn_checkin, emp_ot_checkin, emp_ot_checkout, over_time, ontime, updated_by
FROM timesheet_tran
WHERE emp_id =  'TMSTEST'
AND tran_as_of_date =  '04/02/14'

Returns data as

SQL Structure

I want to find a way to combine and display data

Like

emp_location -- emp_system_name -- morn_check_OTM -- mor_check_DLY -- overtime_OTM --overtime_OT
AMK               ::1                8.45               8.38            1:15           6:30

My SQL knowledge , cant do this :(

What about tran_type_code which value do you want to select OTM or DLY or OT or any value?? — Mahmoud Gamal
– Mahmoud Gamal, Commented Feb 4, 2014 at 12:16
what are the rules to combine the entries? do calculations need to happen or only select based on tran_type_code ? — Jan Prieser
– Jan Prieser, Commented Feb 4, 2014 at 12:20
the vaules need to be in one row .. morn_check_OTM , mor_check_DLY — epynic
– epynic, Commented Feb 4, 2014 at 12:27

Mahmoud Gamal · Accepted Answer · 2014-02-04 12:33:57Z

3

What you are looking for is pivot rows into columns, unfortunately MySQL doesn't have a native pivot table operator, but you can use the CASE expression to do so:

SELECT 
  emp_location, 
  emp_system_name,
  MAX(CASE WHEN tran_type_code = 'OTM' THEN emp_morn_checkin END) AS morn_check_OTM,
  MAX(CASE WHEN tran_type_code = 'DLY' THEN emp_morn_checkin END) AS morn_check_DLY,
  MAX(CASE WHEN tran_type_code = 'OT' THEN emp_morn_checkin END) AS morn_check_OT,
  MAX(CASE WHEN tran_type_code = 'OTM' THEN over_time END) AS over_time_OTM,
  MAX(CASE WHEN tran_type_code = 'DLY' THEN over_time END) AS over_time_DLY,
  MAX(CASE WHEN tran_type_code = 'OT' THEN over_time END) AS over_time_OT
FROM timesheet_tran
WHERE emp_id =  'TMSTEST'
AND tran_as_of_date =  '04/02/14'
GROUP BY emp_location, emp_system_name;

answered Feb 4, 2014 at 12:33

Mahmoud Gamal

80.2k18 gold badges143 silver badges168 bronze badges

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

3 Comments

epynic Over a year ago

From where do we find People like U ..:) cheers Works like Charm

epynic Over a year ago

from were do you know all this stuff ?

Mahmoud Gamal Over a year ago

@epynic From here, from stackoverflow :)

Raghunandan Krishnamurthy · Accepted Answer · 2014-02-04 12:22:11Z

0

Try using concat(a, '---',b,c,d,) the way you want

answered Feb 4, 2014 at 12:22

Raghunandan Krishnamurthy

6955 silver badges11 bronze badges

2 Comments

Raghunandan Krishnamurthy Over a year ago

concat(emp_location, '--', emp_system_name, '--' ,morn_check_OTM ,'--' ,mor_check_DLY, '--', overtime_OTM ,'--',overtime_OT)

epynic Over a year ago

yes but how can i combine the resultant to one single row

Collectives™ on Stack Overflow

SQL combine and join row data with same Table

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related