Change columns to row in SQL table (Salesforce CRM)

Question

I'm using salesforce analytics studio and from my dataset I produce this table:

REGION_NAME	PROFIT	CA	COST
FR	10	20	10
UK	20	40	20

from this query:

SELECT
    REGION_NAME
    ,sum(CA) as CA
    ,sum(COSTS) as COST
    ,(sum(CA) - sum(_COSTS)) as PROFIT
FROM "DATASET_NAME" 
GROUP BY REGION_NAME

However I'm trying to display my value as follows:

Type	FR	UK
PROFIT	10	20
CA	20	40
COST	10	20

How should I update my query ? I suppose I could use PIVOT function but I don't how to apply it in this case.

To create a sample of my dataset:

CREATE TABLE DATASET_NAME (
    CA INT,
    COST INT,
    REGION_NAME VARCHAR(255)
)
INSERT INTO DATASET_NAME (CA, COST, REGION_NAME)
VALUES 
    (10, 5, 'FR'),
    (10, 5, 'FR'),
    (40, 20,'UK');

Answer 1

You actually need an unpivot here, followed by a pivot:

SELECT
    TYPE,
    MAX(CASE WHEN REGION_NAME = 'FR' THEN VAL END) AS FR,
    MAX(CASE WHEN REGION_NAME = 'UK' THEN VAL END) AS UK
FROM (
    SELECT 'PROFIT' AS TYPE, REGION_NAME, PROFIT AS VAL FROM DATASET_NAME
    UNION ALL
    SELECT 'CA', REGION_NAME, CA FROM DATASET_NAME
    UNION ALL
    SELECT 'COST', REGION_NAME, COST FROM DATASET_NAME
) t
GROUP BY TYPE;