Rollup multiple rows into one [duplicate]

Question

I would like to preface this by saying that I'm relatively new to writing SQL. Please bear with my stupidity.

I'm trying to extract data from multiple tables onto a spreadsheet

I have 3 tables that look like so.

Account

id	name	is_setup
1	Acc1	1

Profile

id	account_id(fk)	name	type
1	1	profname	shopify

ProfilePreferences

id	profile_id(fk)	name	value
1	1	is_quote_enabled	1
2	1	auto_sync	1

I have a query that returns what I want but I can't wrap my head around how to get results in the same row rather than them being presented as 2 rows for the settings in the preferences table.

My query looks like so...

SELECT acc.name,
       prof.`integration_type`,
       prof.`name`,
       prof.`status`,
       acc.`is_setup`,
       prof.pref_name,
       prof.`pref_value`,
       sh_acc_id,
       sid
FROM accounts acc
INNER JOIN
    (SELECT prof.account_id AS pid,
            prof.`integration_type`,
            prof.`name`,
            prof.`status`,
            prof_pref.`name` AS pref_name,
            prof_pref.`value` AS pref_value
     FROM profiles prof
     INNER JOIN profile_preferences prof_pref ON prof.`id` = prof_pref.`profile_id`
     AND prof.`integration_type`="shopify"
     WHERE prof_pref.`name`="is_quote_enabled"
         AND prof_pref.`value`= 1
         OR prof_pref.`name`="auto_sync"
         AND prof_pref.`value`= 1
         OR prof_pref.`name`="is_quote_enabled"
         AND prof_pref.`value`= 0
         OR prof_pref.`name`="auto_sync"
         AND prof_pref.`value`= 0) prof ON acc.`id` = pid
WHERE acc.`is_setup` = 1;

The output I get looks like so...

name	integration_type	name	status	is_setup	pref_name	pref_value
Acc1	shopify	profname	1	1	auto_sync	1
Acc1	shopify	profname	1	1	is_quote_enabled	1

Is there a way to output something like this:

name	integration_type	name	status	is_setup	auto_sync	is_quote_enabled
Acc1	shopify	profname	1	1	1	1

Answer 1

Thanks to @mustaccio I was finally able to re-factor the query to output exactly what I wanted

The CASE syntax was incorrect and I was missing the END clause

This is the final query I ran

SELECT acc.`id`,
       acc.`name`,
       acc.`created_at`,
       acc.`is_setup`,
       SUM(CASE prof.pref_name
               WHEN 'auto_sync' THEN prof.`pref_value`
               ELSE 0
           END) AS `auto_sync`,
       SUM(CASE prof.pref_name
               WHEN 'is_quote_enabled' THEN prof.`pref_value`
               ELSE 0
           END) AS `is_quote_enabled`
FROM accounts acc
INNER JOIN
    (SELECT prof.account_id AS profile_account_id,
            prof.`integration_type`,
            prof_pref.`name` AS pref_name,
            prof_pref.`value` AS pref_value
     FROM profiles prof
     INNER JOIN profile_preferences prof_pref ON prof.`id` = prof_pref.`profile_id`
     AND prof.`integration_type`="shopify"
     WHERE prof_pref.`name`="is_quote_enabled"
         AND prof_pref.`value`= 1
         OR prof_pref.`name`="auto_sync"
         AND prof_pref.`value`= 1
         OR prof_pref.`name`="is_quote_enabled"
         AND prof_pref.`value`= 0
         OR prof_pref.`name`="auto_sync"
         AND prof_pref.`value`= 0) prof ON acc.`id` = profile_account_id
WHERE acc.`is_setup` = 1
GROUP BY `acc`.`id`,
         `acc`.`name`,
         `acc`.`created_at`,
         `acc`.`is_setup`;

Now the output produced looks like so...

id	name	created_at	is_setup	auto_sync	is_quote_enabled
1	name	2021-12-09	1	1	1