SQL - Making columns values as column headers for multiple values-CodePudding

I have the following table

ID	key	value
1	vertical	auto
1	subvertical	tires
1	company uid	54321
1	auto debit	true
2	vertical	healthcare
2	subvertical	dentist
2	company uid	12345
2	auto debit	false

The output I would like is to use the values in column key as column headers with values from column value. Meaning I would like to see:

ID	vertical	subvertical	company uid	auto debit
1	auto	tires	54321	true
2	healthcare	dentist	12345	false

I've tried achieving this with the following query:

SELECT payment_intent_id 
       MAX CASE WHEN key = 'podium_location_uid' 
                THEN value END AS "Podium Location UID" 
       MAX CASE WHEN key = 'payment_method_type' 
                THEN value END AS "Payment Method Type" 
       MAX CASE WHEN key = 'customer_name' 
                THEN value END AS "Customer Name" 
       MAX CASE WHEN key = 'sub_vertical' 
                THEN value END AS "Sub Vertical" 
       MAX CASE WHEN key = 'vertical' THEN value END AS "Vertical" 
FROM payment_intents_metadata 
GROUP BY payment_intent_id

but I'm getting the following error:

Malformed query: line 2:6: mismatched input 'case'. Expecting: ',', 'EXCEPT', 'FETCH', 'FROM', 'GROUP', 'HAVING', 'INTERSECT', 'LIMIT', 'OFFSET', 'ORDER', 'UNION', 'WHERE',

How can I accomplish this?

CodePudding user response：

The query you posted has lots of syntax errors -- here is how I would write it

SELECT payment_intent_id, 
       MAX (CASE WHEN key = 'podium_location_uid' THEN value END) AS "Podium Location UID", 
       MAX (CASE WHEN key = 'payment_method_type' THEN value END) AS "Payment Method Type",
       MAX (CASE WHEN key = 'customer_name'       THEN value END) AS "Customer Name",
       MAX (CASE WHEN key = 'sub_vertical'        THEN value END) AS "Sub Vertical",
       MAX (CASE WHEN key = 'vertical'            THEN value END) AS "Vertical" 
FROM payment_intents_metadata 
GROUP BY payment_intent_id

Please note the use of (,), and ,

CodePudding user response：

You either have a version of SQL I've never seen before, or your query is terribly formatted and a basic SQL tutorial should be looked up.

SELECT payment_intent_id, 
       MAX(CASE WHEN key = 'podium_location_uid' 
                THEN value END) AS "Podium Location UID", 
       MAX(CASE WHEN key = 'payment_method_type' 
                THEN value END) AS "Payment Method Type", 
       MAX(CASE WHEN key = 'customer_name' 
                THEN value END) AS "Customer Name", 
       MAX(CASE WHEN key = 'sub_vertical' 
                THEN value END) AS "Sub Vertical", 
       MAX(CASE WHEN key = 'vertical' 
                THEN value END) AS "Vertical" 
FROM payment_intents_metadata 
GROUP BY payment_intent_id