View: project_approval_view

Columns
	Column Name	Data Type
1.	project_key	decimal(15)
2.	leave_request	varchar(1)
3.	time	varchar(1)
4.	expense_request	varchar(1)
5.	expense_report	varchar(1)
6.	purchase_request	varchar(1)
7.	purchase_order	varchar(1)
8.	vendor_invoice	varchar(1)

Table/Column Dependencies
Table	Column
project	project_key
project_controller	approval_type
	primary_ind
	project_key
	role_key

SQL Server Create Statement
create view project_approval_view as select p.project_key, coalesce(max(case when pc.approval_type = 1 then 'Y' else null end), 'N') as leave_request, coalesce(max(case when pc.approval_type = 2 then 'Y' else null end), 'N') as time, coalesce(max(case when pc.approval_type = 3 then 'Y' else null end), 'N') as expense_request, coalesce(max(case when pc.approval_type = 4 then 'Y' else null end), 'N') as expense_report, coalesce(max(case when pc.approval_type = 5 then 'Y' else null end), 'N') as purchase_request, coalesce(max(case when pc.approval_type = 6 then 'Y' else null end), 'N') as purchase_order, coalesce(max(case when pc.approval_type = 7 then 'Y' else null end), 'N') as vendor_invoice from project p left outer join project_controller pc on pc.project_key = p.project_key where pc.primary_ind = 'Y' and pc.role_key = 13 group by p.project_key

SQL Server Create Statement

create view project_approval_view as
select
   p.project_key,
   coalesce(max(case when pc.approval_type = 1 then 'Y' else null end), 'N') as leave_request,
   coalesce(max(case when pc.approval_type = 2 then 'Y' else null end), 'N') as time,
   coalesce(max(case when pc.approval_type = 3 then 'Y' else null end), 'N') as expense_request,
   coalesce(max(case when pc.approval_type = 4 then 'Y' else null end), 'N') as expense_report,
   coalesce(max(case when pc.approval_type = 5 then 'Y' else null end), 'N') as purchase_request,
   coalesce(max(case when pc.approval_type = 6 then 'Y' else null end), 'N') as purchase_order,
   coalesce(max(case when pc.approval_type = 7 then 'Y' else null end), 'N') as vendor_invoice
from project p
left outer join project_controller pc on pc.project_key = p.project_key
where pc.primary_ind = 'Y'
  and pc.role_key = 13
group by p.project_key