0

This question already has an answer here:

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?

marked as duplicate by Mikael Eriksson, Andriy M, Erik, Philᵀᴹ, RolandoMySQLDBA Apr 7 '16 at 16:20

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

0

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;

Not the answer you're looking for? Browse other questions tagged or ask your own question.