MySQL Row Output Cells into Columns

Question

My question is how do you get row cell output into separate columns to make viewing that data more readable.

I have the following SQL Query:

SELECT TD.name AS Conditions, PV.value AS Frequency, MSL.name AS  
Mailing_List_Subscriptions, Count(CI.uid) AS Users_Signup_Count
FROM conditions_interest CI
INNER JOIN profile_values PV
ON CI.uid = PV.uid
INNER JOIN hwmailservice_user_lists MSUL
ON CI.uid = MSUL.uid
INNER JOIN hwmailservice_lists MSL
ON MSUL.list_id = MSL.list_id
INNER JOIN term_data TD
ON CI.tid = TD.tid
WHERE (PV.value = 'daily' OR PV.value = 'weekly') AND CI.email = '1'
GROUP BY PV.value, TD.name, MSL.name
ORDER BY TD.name;

With the following output:

SQL OUTPUT

So all the mailing list subscriptions would have there own separate column with the counts associated with the conditions. So like this:

Conditions Frequency Newsletter Partners Annoucements marketing
Abscessed Tooth Daily 95 91 98 98
Abscessed Tooth Weekly 6 4 7 7

If more clarification is needed I will edit my post.

Taryn · Accepted Answer · 2012-08-03 17:51:40Z

3

MySQL does not have a PIVOT function which is what you are doing, so you will want to use a CASE:

SELECT x.Conditions,
  x.Frequency,
  SUM(CASE WHEN Mailing_List_Subscriptions = 'newsletter' THEN Users_Signup_Count END) newsletter,
  SUM(CASE WHEN Mailing_List_Subscriptions = 'partners' THEN Users_Signup_Count END) partners,
  SUM(CASE WHEN Mailing_List_Subscriptions = 'announcements' THEN Users_Signup_Count END) announcements,
  SUM(CASE WHEN Mailing_List_Subscriptions = 'marketing' THEN Users_Signup_Count END) marketing
FROM
(
  SELECT TD.name AS Conditions, PV.value AS Frequency, 
    MSL.name AS  Mailing_List_Subscriptions, 
    Count(CI.uid) AS Users_Signup_Count
  FROM conditions_interest CI
  INNER JOIN profile_values PV
    ON CI.uid = PV.uid
  INNER JOIN hwmailservice_user_lists MSUL
    ON CI.uid = MSUL.uid
  INNER JOIN hwmailservice_lists MSL
    ON MSUL.list_id = MSL.list_id
  INNER JOIN term_data TD
    ON CI.tid = TD.tid
  WHERE (PV.value = 'daily' OR PV.value = 'weekly') AND CI.email = '1'
  GROUP BY PV.value, TD.name, MSL.name
) x
GROUP BY x.Conditions, x.Frequency
ORDER BY x.name

answered Aug 3, 2012 at 17:51

Taryn

249k57 gold badges374 silver badges409 bronze badges

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

3 Comments

rs. Over a year ago

you missed group by clause for outer query

Taryn Over a year ago

@rs just realized and added it

Novazero Over a year ago

Awesome it worked your a lifesaver! Will give you the accepted answer once 6 minutes pass haha.

Collectives™ on Stack Overflow

MySQL Row Output Cells into Columns

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related