SQL Server Pivot Multiple Column and Value I have a sample table attendance look like this :

PERIOD  EMPL_NMBR   EMPL_NAME           DRPT     DAYX    [IN ]    [OUT]
201603  00001       John                QA       01      08:00    17:00
201603  00001       John                QA       02      08:00    17:00
201603  00001       John                QA       03     
201603  00001       John                QA       04      08:00    17:00
201603  00001       Susan               SALES    01      08:00    17:00
201603  00001       Susan               SALES    02     
201603  00001       Susan               SALES    03      08:00    17:00
201603  00001       Susan               SALES    04     


I'd like to display the data from that table like this:

PERIOD     EMPL_NMBR     EMPL_NAME      1           2            3             4
                                   [IN]  [OUT]  [IN]  [OUT]  [IN]  [OUT]   [IN]  [OUT]
201603     00001         John     08:00  17:00  08:00  17:00  -      -     08:00  17:00
201603     00002         Susan    08:00  17:00   -      -    08:00  17:00    -      -


I can successfully pivot it using a single column and single value dynamically, but I get confused when I try to pivot multiple columns and values dynamically.

Can anyone help me?
If you have to do it on the SQL Server side, you can use GROUP BY with a CASE statement:

SELECT PERIOD, EMPL_NMBR, EMPL_NAME
    , IN_1 = MAX(CASE WHEN DAYX = 1 THEN [IN] END)
    , OUT_1 = MAX(CASE WHEN DAYX = 1 THEN [OUT] END)
    , IN_2 = MAX(CASE WHEN DAYX = 2 THEN [IN] END)
    , OUT_2 = MAX(CASE WHEN DAYX = 2 THEN [OUT] END)
    , IN_3 = MAX(CASE WHEN DAYX = 3 THEN [IN] END)
    , OUT_3 = MAX(CASE WHEN DAYX = 3 THEN [OUT] END)
    , IN_4 = MAX(CASE WHEN DAYX = 4 THEN [IN] END)
    , OUT_4 = MAX(CASE WHEN DAYX = 4 THEN [OUT] END)
FROM @data
GROUP BY PERIOD, EMPL_NMBR, EMPL_NAME;