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.

is the available flags only 'N' and 'D'?

Abdul Rasheed
– Abdul Rasheed

2017-11-14 06:38:00 +00:00
Commented Nov 14, 2017 at 6:38 — Abdul Rasheed
– Abdul Rasheed, Commented Nov 14, 2017 at 6:38

Jayasurya Satheesh · Accepted Answer · 2017-11-14 06:38:41Z

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

answered Nov 14, 2017 at 6:38

Jayasurya Satheesh

8,0733 gold badges25 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to pivot on SQL-Server multiple columns?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related