How to pivot on SQL-Server multiple columns?

Question

I am trying to create a SQL Statement to pivot a variable number of rows into a single row. A small subset of the data looks like this:

+--------------+--------+-------+-------+
|      ID      | Flag   | workD | holiD |
+--------------+--------+-------+-------+
| 11155        | N      | 1     | 0     |
| 11155        | D      | 1     | 1     |
| 5675         | N      | 1     | 1     |
| 98761        | N      | 0     | 1     |
| 98761        | D      | 1     | 1     |
+--------------+--------+-------+-------+

and I would like to pivot the data to look like the following:

+--------------+---------+---------+---------+---------+
|      ID      | N_wordD | N_holiD | D_wordD | D_holiD |
+--------------+---------+---------+---------+---------+
| 11155        | 1       | 0       | 1       | 1       |
| 5675         | 1       | 1       | NULL    | NULL    |
| 98761        | 0       | 1       | 1       | 1       |
+--------------+---------+---------+---------+---------+

I am a bit lost when it comes to pivoting, particularly when I want to pivot both the Style and the Quantity into my columns.

Any suggestions, pointers, etc would be greatly appreciated.

Answer 1

I Don't think you need a pivot here, Instead you can try this using a combination of Case and Group By. try the Below

WITH CTE
AS
(
    SELECT
    ID,
    N_WorkD = CASE WHEN Flag = 'N' THEN WorkD ELSE 0 END ,
    N_holiD = CASE WHEN Flag = 'N' THEN holiD ELSE 0 END,
    D_WorkD = CASE WHEN Flag = 'D' THEN WorkD ELSE 0 END ,
    D_holiD = CASE WHEN Flag = 'D' THEN holiD ELSE 0 END
    FROM T1
)
SELECT
  ID,
  N_WorkD = SUM(N_WorkD),
  N_holiD = SUM(N_holiD),
  D_WorkD = SUM(D_WorkD),
  D_holiD = SUM(D_holiD)
  FROM CTE
    GROUP BY ID

Sample Result is as below

Check the SQLFiddle Here