1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48

We have the following table that stores the access information for a project;

The access_type represents access to a particular project.. i.e. 1 means standard access for a project, 2 might mean advanced access.

The underlying table looks like this:

access_type  person_id project_id data_source
1             120352    some39873  the_finance_system
2             120352    some39873  the_finance_system
1             123352    some39873  the_hr_system


We need to display all the access types on a single row so that it looks like this

person_id, project_id, standard_access, standard_access_data_source, advanced_access, advanced_access_data_source
120352       some39873  1                the_finance_system           1                the_finance_system
123352      some39873   1                the_hr_system                0


so we do a group by
SELECT
  person_id,
  project_id,
  sum(decode(access_type,1,1,0) as standard_access,
  sum(decode(access_type,2,1,0) as advanced_access
FROM 
 access_table
group by person_id, project_id


and this gets us everything but the data_sources.. which i've got no idea how i can get

WITH a AS(
SELECT
  person_id,
  project_id,
  sum(decode(access_type,1,1,0) as standard_access,
  sum(decode(access_type,2,1,0) as advanced_access
FROM 
 access_table
group by person_id, project_id)
SELECT person_id,
       project_id,
       standard_access,
       advanced_access,
       DECODE(standard_access,....) AS ,
       DECODE(advanced_access,....) AS
FROM a,...