Transpose columns to rows and replace values by binary

Question

I have the following table :

Team Department1 Department2 Department3
1    Marketing   Sales       Finance
2    Sales       IT          HR

I want to transpose data like below :

Team Marketing Sales  Finance IT HR
1    1         1      1       0  0
2    0         1      0       1  1

Gordon Linoff · Accepted Answer · 2021-03-11 13:05:14Z

2

You can use case logic:

select team,
       (case when 'Marketing' in (Department1, Department2, Department3) then 1 else 0
        end) as marketing,
       (case when 'Sales' in (Department1, Department2, Department3) then 1 else 0
        end) as sales,
       (case when 'Finance' in (Department1, Department2, Department3) then 1 else 0
        end) as finance,
       (case when 'IT' in (Department1, Department2, Department3) then 1 else 0
        end) as it,
       (case when 'HR' in (Department1, Department2, Department3) then 1 else 0
        end) as hr
from t;

answered Mar 11, 2021 at 13:05

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Comments

user9517769 · Accepted Answer · 2021-03-11 13:06:29Z

1

SELECT Team,
       (CASE WHEN 'Marketing' IN (Department1, Department2, Department3) THEN 1 else 0
        end) AS Marketing
...
FROM yourtable;

answered Mar 11, 2021 at 13:06

user9517769

Comments

Charlieface · Accepted Answer · 2021-03-11 13:21:35Z

1

Unpivot your table first, then it's pretty simple conditional aggregation.

select team,
       count(case when v.Department = 'Marketing' then 1 end) as marketing,
       count(case when v.Department = 'Sales' then 1 end) as sales,
       count(case when v.Department = 'Finance' then 1 end) as finance,
       count(case when v.Department = 'IT' then 1 end) as it,
       count(case when v.Department = 'HR' then 1 end) as hr
from t
cross apply (
    (Department1),
    (Department2),
    (Department3)
) v(Department)
group by team;

You table design is faulty. You should have TeamDepartment as a separate many-to-many linking table

answered Mar 11, 2021 at 13:21

Charlieface

78.6k8 gold badges35 silver badges77 bronze badges

Collectives™ on Stack Overflow

Transpose columns to rows and replace values by binary

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related