SQL - Making columns values as column headers for multiple values

Question

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?

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 — Kaiah Nelson
– Kaiah Nelson, Commented Jun 15, 2022 at 17:07
This is the last thing I tried, however I received this error "Malformed query: line 2:6: mismatched input 'case'. Expecting: ',', 'EXCEPT', 'FETCH', 'FROM', 'GROUP', 'HAVING', 'INTERSECT', 'LIMIT', 'OFFSET', 'ORDER', 'UNION', 'WHERE', <EOF> — Kaiah Nelson
– Kaiah Nelson, Commented Jun 15, 2022 at 17:08
What RDMS are you using? Your query doesn't look like valid SQL to me... — BeRT2me
– BeRT2me, Commented Jun 15, 2022 at 17:59

Hogan · Accepted Answer · 2022-06-15 18:03:40Z

1

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 ,

answered Jun 15, 2022 at 18:03

Hogan

70.7k10 gold badges83 silver badges119 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

BeRT2me · Accepted Answer · 2022-06-15 18:03:51Z

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

Collectives™ on Stack Overflow

SQL - Making columns values as column headers for multiple values

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related