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

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

CodePudding user response：

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;