rows to columns
Posted: Mon Nov 15, 2010 4:25 am
source:
DAY_ID | WK_END_DT | ACT_ID|ACT_START_TIME| ACT_END_TIME| TGT_EMP_ID
2010-11-07| 2010-11-13| WRK | 22:00:00.0| 06:30:00.0| 11267
2010-11-07| 2010-11-13| BRK | 23:30:00.0| 23:45:00.0| 11267
2010-11-07| 2010-11-13| MEAL | 01:30:00.0| 02:00:00.0| 11267
2010-11-07| 2010-11-13| BRK | 04:30:00.0| 04:45:00.0| 11267
Target:
DAY_ID | WK_END_DT | WRK | BRK1 | BRK2 | BRK3 | MEAL1 | MEAL2
2010-11-07| 2010-11-13 |22:00:00.0 |23:30:00.0|04:30:00.0 | - |01:30:00.0 | -
I want to convert the data coming from source as given above into columns as shown as target. The BRK ACT_ID can have the multiple option of breaks and the MEAL activity can also have multiple meals. In practical there can be three breaks and two meals. It would be great if somebody suggest how to achieve the target output.
DAY_ID | WK_END_DT | ACT_ID|ACT_START_TIME| ACT_END_TIME| TGT_EMP_ID
2010-11-07| 2010-11-13| WRK | 22:00:00.0| 06:30:00.0| 11267
2010-11-07| 2010-11-13| BRK | 23:30:00.0| 23:45:00.0| 11267
2010-11-07| 2010-11-13| MEAL | 01:30:00.0| 02:00:00.0| 11267
2010-11-07| 2010-11-13| BRK | 04:30:00.0| 04:45:00.0| 11267
Target:
DAY_ID | WK_END_DT | WRK | BRK1 | BRK2 | BRK3 | MEAL1 | MEAL2
2010-11-07| 2010-11-13 |22:00:00.0 |23:30:00.0|04:30:00.0 | - |01:30:00.0 | -
I want to convert the data coming from source as given above into columns as shown as target. The BRK ACT_ID can have the multiple option of breaks and the MEAL activity can also have multiple meals. In practical there can be three breaks and two meals. It would be great if somebody suggest how to achieve the target output.