sqlmariadbmariadb-10.5

How to bring results from rows into columns with respective values in SQL

I currently have the below results table:

Company ID External ID Attribute Int Value
1 101 Calls 3
1 101 Emails 14
1 101 Accounts 4
2 102 Calls 2
2 102 Emails 17
2 102 Accounts 5

And I would like to transform my query results to show as below:

Company ID External ID Calls Emails Accounts
1 101 3 14 4
2 102 2 17 5

Is this possible and if so, how would I do this? I'm a new SQL user and can't seem to figure this out :)

This is my current query to get my results:

SELECT
 ic.company_id,
 ic.external_id,
 icca.attribute, 
 icca.int_value


FROM
 intercom_companies AS ic


LEFT JOIN intercom_companies_custom_attributes AS icca 
ON ic.company_id = icca.company_id
Solution

  • A pivot query should work here:

    SELECT
    ic.company_id,
    ic.external_id,
    MAX(CASE WHEN icca.attribute = 'Calls'
             THEN icca.int_value END) AS Calls,
    MAX(CASE WHEN icca.attribute = 'Emails'
             THEN icca.int_value END) AS Emails,
    MAX(CASE WHEN icca.attribute = 'Accounts'
             THEN icca.int_value END) AS Accounts
FROM intercom_companies AS ic
LEFT JOIN intercom_companies_custom_attributes AS icca
    ON ic.company_id = icca.company_id
GROUP BY
    ic.company_id,
    ic.external_id;