Modifying this Database query to split out Project roles for ingesting into Insight

[Steve Letch]

Hi Guys

This DB query from the Atlassian site is pretty cool, however I need a tweak if any geniuses could help.

SELECT 
p.id, 
p.pname, 
pr.NAME, 
u.display_name, 
p.pkey 
FROM 
projectroleactor pra 
INNER JOIN projectrole pr ON pr.ID = pra.PROJECTROLEID 
INNER JOIN project p ON p.ID = pra.PID 
INNER JOIN app_user au ON au.user_key = pra.ROLETYPEPARAMETER 
INNER JOIN cwd_user u ON u.lower_user_name = au.lower_user_name 
where 
name = 'Administrators' 
or name = 'Business Owners';

The issue I have is that all that both role types end up in the same column, when what I'd really like is for there to be a column for each role that I'd like/may add in the future. So one column for the Administrators and one for the Business Owners.

The reason for this is I really want to have this info in my Insight schema, but it's seemingly impossible to split out the data within the same import module, or having to massively over engineer by trying to use a separate objecttype and inbound references.

Oh and I know DB queries have a tendency to leave out a row if a part of it doesnt completely match the query, so I'd need it to return a row, even if there's only Administrators and not Business Owners.

thanks

[Tom Lister · Accepted solution]

Hi @Steve Letch 

This is my best shot.

select id, pname, pkey, NAME, Administrators, "Project Members" from (
SELECT 
p.id, 
p.pname, 
pr.NAME, 
u.display_name as Administrators, '' as "Project Members",
p.pkey 
FROM 
projectroleactor pra 
INNER JOIN projectrole pr ON pr.ID = pra.PROJECTROLEID 
INNER JOIN project p ON p.ID = pra.PID 
INNER JOIN app_user au ON au.user_key = pra.ROLETYPEPARAMETER 
INNER JOIN cwd_user u ON u.lower_user_name = au.lower_user_name 
where 
name = 'Administrators' 
--or name = 'Business Owners'
union
SELECT 
p.id, 
p.pname, 
pr.NAME, 
'' as Administrators,
u.display_name as "Project Members", 
p.pkey 
FROM 
projectroleactor pra 
INNER JOIN projectrole pr ON pr.ID = pra.PROJECTROLEID 
INNER JOIN project p ON p.ID = pra.PID 
INNER JOIN app_user au ON au.user_key = pra.ROLETYPEPARAMETER 
INNER JOIN cwd_user u ON u.lower_user_name = au.lower_user_name 
where 
--name = 'Administrators' 
name = 'Project Members'
) as results 
group by id, pname, pkey, name, Administrators, "Project Members"

[Steve Letch]

Hm, still seems like a one or the other unfrotunately.

It's ok, thanks for trying :)