Pivot Table with a condition-CodePudding

Consider I have a table like this

ID  DEPARTMENT
1   Production
1   IT
2   Sales
2   Marketing
2   Sales

How can I get a result like below

ID Production IT Sales Marketing
1  1          1  0     0
2  0          0  1     1
2  0          0  1     0

You will notice that in the original table for ID number 2 'Sales' department is repeated, so whenever a department is repeated there should be another entry in the pivoted table.

CodePudding user response：

One way is by adding a row_number before pivoting.
Then the pivot won't just group by ID.

SELECT ID, Production, IT, Sales, Marketing
FROM
(
  SELECT ID, DEPARTMENT
  , ROW_NUMBER() OVER (PARTITION BY ID, DEPARTMENT ORDER BY ID) AS RN
  FROM yourtable t
) Src
PIVOT 
(
   COUNT(*)
   FOR DEPARTMENT IN (
     'Production' AS Production,
     'IT' AS IT,
     'Sales' AS Sales, 
     'Marketing' AS Marketing
    ) 
) Pvt

ID	PRODUCTION	IT	SALES	MARKETING
1	1	1	0	0
2	0	0	1	1
2	0	0	1	0

db<>fiddle here