Display rows from one column as headings on the query result and sum data for each column from another table



  • I'm going to do my best to describe this. So I have 3 tables as follows:

    project

    id title department_id
    1001 Project One 50
    1002 Project two 50

    metric

    id name
    101 Number of exhibits
    102 Number of stakeholders

    project_metric

    id metric_id project_id value
    1 101 1001 10
    2 101 1002 20
    3 102 1001 5
    4 102 1002 33

    What I'm trying to do is write a query that will produce the below result.

    project_title Number of exhibits Number of stakeholders
    Project One 10 5
    Project Two 20 33

    I'm not going to paste any of my attempts because they're pathetic lol. A couple of notes:

    1. PostgreSQL version is 9.5 on this db.
    2. (IMPORTANT) This query will be implemented inside a long query that displays some 65 columns of information about a Project, but for brevity obviously I've omitted some project attributes. I'll post a partial snippet of the current query.
    SELECT DISTINCT
        p.title AS "Project Title", d.name AS "Center/Department",
    

    // This is a query that currently exists in the long query I mentioned.
    (SELECT DISTINCT string_agg(DISTINCT re.name, ', ') AS "Research Exchange"
    FROM research_exchange re
    JOIN project_research_exchange pre ON pre.research_exchange_id = re.id
    WHERE pre.project_id = p.id)

    FROM project p
    LEFT JOIN department d ON p.department_id = d.id
    GROUP BY p.title
    ORDER BY p.title

    Thanks in advance good people.



  • You could try:

    select title,
        max(case when  name='Number of exhibits' then value end ) as "Number of exhibits",
        max(case when  name='Number of stakeholders' then value end ) as "Number of stakeholders"
    from (
        select p.title,pm.value,m.name
        from project p
        left join project_metric pm on p.id=project_id
        left join metric m on m.id=pm.metric_id
    ) t1
    group by title
    order by title asc;    
    

    Result

    title Number of exhibits Number of stakeholders
    Project One 10 5
    Project two 20 33

    https://dbfiddle.uk/?rdbms=postgres_9.6&fiddle=6788f727040b5ae98d43b7aa3fa74400


Log in to reply
 


Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2