Convert column to Row for query with joins MYSQL

Question

How do I convert the GROUP_CONCAT row result as a separate columns for this query?

SELECT Email, Address, CityName, StateName, PostalCode,
GROUP_CONCAT(DISTINCT
    CONCAT(
      PP.PrefKey
    )
  )
    FROM Profile P 
    LEFT JOIN ProfileAddress PA ON  P.ProfileID = PA.ProfileID
     LEFT JOIN ProfilePrefs PP   ON PP.ProfileID = PA.ProfileID
    WHERE P.ProfileID = 6;

DESIRED OUTPUT OF QUERY RESULT

ProfilePrefs table

Provide desired output. PS. It seems that you want PIVOT - this is not supported by MySQL. Conditional aggregate may help. — Akina, Nov 19 '20 at 08:41
yes I'm looking for a pivot to change it into column names , how to achieve through aggregate?@Akina — art, Nov 19 '20 at 08:43
Does there exists complete list of `PrefKey` values somewhere? — Akina, Nov 19 '20 at 08:45
Yes in ProfilePrefs Table there is a column PrefKey, attached screenshot in question.@Akina — art, Nov 19 '20 at 08:46
"this query" is a broken reference, because you deleted what it refers to in you most recent edit. Please undo the edit or otherwise fix the broken reference. Make sure that the question still matches the answers you got. Changing a question so that an existing answer seems invalid is considered a "moving target" question and not appreciated. For now I will simply roll back your edit, for you to do the fine tuning. — Yunnosch, Nov 19 '20 at 09:21

score -1 · Answer 1 · answered Nov 19 '20 at 08:39

-1

you can use aliases. if i understand your question, your answer is :

SELECT Email, Address, CityName, StateName, PostalCode,
GROUP_CONCAT(DISTINCT
    CONCAT(
      PP.PrefKey
    )
  ) AS SAMPLE_COLUMN_NAME
    FROM Profile P 
    LEFT JOIN ProfileAddress PA ON  P.ProfileID = PA.ProfileID
     LEFT JOIN ProfilePrefs PP   ON PP.ProfileID = PA.ProfileID
    WHERE P.ProfileID = 6;

and you can use SAMPLE_COLUMN_NAME in your query. you can read more here

answered Nov 19 '20 at 08:39

Mahdiyar Zerehpoush

356
5
18

1

I Don't want to rename the column I want them to be changed to columns, please check output – art Nov 19 '20 at 08:45

Akina · Answer 2 · 2020-11-19T08:51:47.850

-1

Pivotting via conditional aggregation looks like

SELECT Email, Address, CityName, StateName, PostalCode,
       MAX(PP.PrefKey = 'availability') availability,
       MAX(PP.PrefKey = 'certifications') certifications,
       ...
FROM Profile P 
LEFT JOIN ProfileAddress PA ON  P.ProfileID = PA.ProfileID
LEFT JOIN ProfilePrefs PP   ON PP.ProfileID = PA.ProfileID
WHERE P.ProfileID = 6 /* may select a list of IDs or remove for to get all IDs */
GROUP BY Email, Address, CityName, StateName, PostalCode;

Of course the complete list of PP.PrefKey values must exist for hardcoding.

If such list is dynamic then only stored procedure with dynamic SQL may solve.

edited Nov 19 '20 at 08:51

answered Nov 19 '20 at 08:49

Akina

39,301
5
14
25

Cant it be dynamic ? – art Nov 19 '20 at 08:49
@art In single query form - no. – Akina Nov 19 '20 at 08:50
and under the column PrefKey can we get PrefValue rather than 1 ? @Akina – art Nov 19 '20 at 08:51
like under Availability I get 2, under certifications i get [] – art Nov 19 '20 at 08:52
@art Replace simple condition with according CASE and assign any value you need in. Maybe even replace MAX() with, for example, GROUP_CONCAT(CASE). – Akina Nov 19 '20 at 08:53
could you please tell how , becuase for me it is returning complete aggregated result under one column – art Nov 19 '20 at 08:55
Create some sample fiddle ( for example, at https://dbfiddle.uk/?rdbms=mysql_8.0 ), add the link into the question, show desired output for this fiddle. – Akina Nov 19 '20 at 08:58
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/224775/discussion-between-art-and-akina). – art Nov 19 '20 at 08:59

Convert column to Row for query with joins MYSQL

2 Answers2