MySQL Forums
Forum List  »  Newbie

Rows to Columns without duplicate rows
Posted by: Jassim Rahma
Date: July 01, 2021 11:20AM


I have the following result:

BusinessID. Activity_Code. Total
11. V 3
11. W 7
22. V 9
22. W 12
33. V 2
33. W 5

but i want to change the result to this:

BusinessID. Activity_V. Activity_W
11. 3 7
22. 9 12
33. 2 5

I tried below code but doesn't work specially with the GROUP BY because if I keep the group then I will have duplicate rows for every buisness ID and if I remove it then it will gove the total for Activity not as per what i want it to be separately (Activity_V and Activity_W)

Kindly help...

SELECT DISTINCT business_activity.business_id, MONTHNAME(business_activity.created_date) as activity_month, YEAR(business_activity.created_date) as activity_year,
business_directory.business_name, activity_category.activity_description,
COUNT(business_activity.business_id) AS total_activity, users.first_name_en AS first_name,
users.last_name_en AS last_name, users.email_address,
COALESCE(COUNT(activity_category.activity_code = 'V'), 0) Activity_V,
COALESCE(COUNT(activity_category.activity_code = 'W'), 0) Activity_W
FROM business_activity
JOIN business_directory on business_directory.business_id = business_activity.business_id
JOIN activity_category ON activity_category.activity_code = business_activity.activity_category
JOIN users ON users.user_id = business_directory.owner_id
WHERE business_directory.business_status = "A"
-- AND YEAR(business_activity.created_date) = YEAR(CURRENT_DATE - INTERVAL 1 MONTH)
-- AND MONTH(business_activity.created_date) = MONTH(CURRENT_DATE - INTERVAL 1 MONTH)
GROUP BY business_activity.business_id -- business_activity.activity_category
ORDER BY business_activity.business_id;

Options: ReplyQuote

Written By
Rows to Columns without duplicate rows
July 01, 2021 11:20AM

Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.