MYSQL Query Transpose Row to Column

Category: Programming Written by Naldi Hits: 360

Transpose Row to column with RowNumber :

SELECT
a.badge_no, 
a.name, 
SUM(IF(RowNumber=1, st, 0)) AS ST1,
SUM(IF(RowNumber=1, ot, 0)) AS OT1,
SUM(IF(RowNumber=2, st, 0)) AS ST2,
SUM(IF(RowNumber=2, ot, 0)) AS OT2,
SUM(st) AS Total_ST,
SUM(ot) AS Total_OT
FROM (
SELECT t. * , @cur := IF( badge_no = @badge_no , @cur +1, 1 ) AS RowNumber, @badge_no := badge_no
FROM hr_ts t
CROSS JOIN (
SELECT @badge_no := ( 
SELECT MIN( badge_no ) 
FROM hr_ts ) , @cur :=0
) AS init
ORDER BY t.badge_no
)a
GROUP BY badge_no


Transpose Row to column without RowNumber :

SELECT
badge_no,
name,
SUM(IF(only_date=01, st, 0)) AS ST1,
SUM(IF(only_date=01, ot, 0)) AS OT1,
SUM(IF(only_date=02, st, 0)) AS ST2,
SUM(IF(only_date=02, ot, 0)) AS ST2,
SUM(st) AS Total_ST,
SUM(ot) AS Total_OT
FROM hr_timesheet
GROUP BY badge_no, only_month
ORDER BY date ASC

------------------ OR ---------------------

SELECT
tanggal,
month,
year,
badge_no, 
name,
MAX(CASE WHEN tanggal=1 THEN st END) AS T1,
MAX(CASE WHEN tanggal=2 THEN st END) AS T2,
MAX(CASE WHEN tanggal=3 THEN st END) AS T3,
MAX(CASE WHEN tanggal=4 THEN st END) AS T4,
MAX(CASE WHEN tanggal=5 THEN st END) AS T5
FROM hr_timesheet
GROUP BY badge_no, month