sql - report records are in multiple systems

Question

Working in SQL I have a dataset of source systems and the ID of people who have access, simplified it looks like this:

System	ID
Sys1	A
Sys2	A
Sys1	B
sys2	C

What I want to get out is something like this:

ID	Sys1	Sys2
A	1	1
B	1	0
C	0	1

I am sure someone has a solution to this here already, but my searching didn't turn up anything.

标签： sqlapache-sparkapache-spark-sql

Answer 1

if it is limited to handful of system values you can use this query:

select
  ID
  , MAX(case when system = 'Sys1' then 1 else 0 end) Sys1
  , MAX(case when system = 'Sys2' then 1 else 0 end) Sys2
  , MAX(case when system = 'Sys3' then 1 else 0 end) Sys3
  , MAX(case when system = 'Sys4' then 1 else 0 end) Sys4
  , ....
from table
GROUP BY ID