Pivoting Text Values in SQL Server as 1 & 0

Question

In MS SQL Server 2014, I have the following EMPLOYEE table that tracks employee actions, such as hiring, leaves, rehiring, etc:

EMPID   EMPNAME     ACTION_DATE ACTION_TYPE
1234    JOHN SMITH  2021-05-01  HIRED
1234    JOHN SMITH  2021-05-01  HIRED
1234    JOHN SMITH  2021-07-31  TERMINATED
1236    JANE SMITH  2021-06-01  HIRED
1236    JANE SMITH  2021-06-15  TERMINATED
1236    JANE SMITH  2021-07-31  REHIRED

I need to:

Pivot the action_type into COLUMN values
Represent the action_type as a "1" if not null and 0 if null.
Ensure duplicates are excluded, as they exist in the source table
Static PIVOT is okay, since there's a limited number of ACTION_tYPE values

Desired outcome:

EMPID   EMPNAME     ACTION_DATE HIRED    TERMINATED    REHIRED
1234    JOHN SMITH  2021-05-01  1        1             0
1236    JANE SMITH  2021-06-15  1        1             1

I'm stuck on this. Here's a sample dataset, and code I've already built/tried. Any suggestions? Thanks!

WITH EMPLOYEES(EMPID, EMPNAME, ACTION_DATE,  ACTION_TYPE )AS
(
   SELECT 1234,  'JOHN SMITH', '2021-05-01', 'HIRED' UNION ALL
   SELECT 1234,  'JOHN SMITH'  , '2021-05-01', 'HIRED' UNION ALL
   SELECT 1234,  'JOHN SMITH' ,'2021-07-31', 'TERMINATED' UNION ALL   
   SELECT 1236,  'JANE SMITH', '2021-06-01', 'HIRED' UNION ALL
   SELECT 1236,  'JANE SMITH'  ,'2021-06-15', 'TERMINATED' UNION ALL
   SELECT 1236,  'JANE SMITH' ,'2021-07-31', 'REHIRED' 
) 


   select *   from EMPLOYEES 
   PIVOT (MAX(ACTION_TYPE) 
   FOR ACTION_TYPE IN ([HIRED],[TERMINATED],[REHIRED])) AS P1

Daniel Gimenez · Accepted Answer · 2021-11-08T19:06:56.317

You can use what you already have, and do a case statement for each pivoted column, or you can use count as the aggregate function and to ensure the result doesn't exceed 1 by using distinct:

SELECT EMPID, EMPNAME, ACTION_DATE, HIRED, TERMINATED, REHIRED 
-- Make sure each row is distinct:
FROM (SELECT DISTINCT EMPID, EMPNAME, ACTION_DATE, ACTION_TYPE FROM EMPLOYEES) emps 
PIVOT (COUNT(ACTION_TYPE) -- At most there can be one row so count can only be 0 or 1.
  FOR ACTION_TYPE IN ([HIRED],[TERMINATED],[REHIRED])) AS P1

I'm not quite sure what you want to do with that ACTION_DATE, as it seems in your example you consolidate everything into one row. In the example below I just use MIN, which doesn't quite match what you're going for, but should be enough guidance to achieve what you want:

SELECT EMPID, EMPNAME, MIN(ACTION_DATE), SUM(HIRED), SUM(TERMINATED), SUM(REHIRED)
FROM (SELECT DISTINCT * FROM EMPLOYEES) emps
PIVOT (COUNT(ACTION_TYPE) 
  FOR ACTION_TYPE IN ([HIRED],[TERMINATED],[REHIRED])) AS P1 
GROUP BY EMPID, EMPNAME

Pivoting Text Values in SQL Server as 1 & 0

1 Answers1