0

I'm trying to fetch a report. Below is the query which I tried.

    SELECT
u.firstname AS FIRST,
u.lastname AS LAST, 
c.fullname AS Course, 
st.attempt AS Attempt,
st.value AS STATUS
FROM prefix_scorm_scoes_track AS st 
JOIN prefix_user AS u ON st.userid=u.id
JOIN prefix_user_info_data AS uid ON uid.userid = u.id 
JOIN prefix_scorm AS sc ON sc.id=st.scormid
JOIN prefix_course AS c ON c.id=sc.course
WHERE st.element='cmi.core.total_time' AND st.element='cmi.core.lesson_status' and c.id=1
UNION
SELECT
user2.firstname AS FIRST,
user2.lastname AS LAST,
c.fullname AS Course,
"-" AS Attempt,
"not_started" AS STATUS
FROM prefix_user_enrolments AS ue
JOIN prefix_enrol AS e ON e.id = ue.enrolid
JOIN prefix_course AS c ON c.id = e.courseid
JOIN prefix_user AS user2 ON user2 .id = ue.userid
JOIN prefix_user_info_data AS uid ON uid.userid = user2.id 
JOIN prefix_scorm AS sc ON sc.course=c.id
LEFT JOIN prefix_scorm_scoes_track AS st ON st.scormid=sc.id AND st.userid=user2.id

WHERE st.timemodified IS NULL AND c.id=1

ORDER BY  Course, LAST, FIRST, Attempt

What I'm looking for is, I want the values of cmi.core.total_time and cmi.core.lesson_status in two columns. But in the database it's stored as row:

enter image description here

How Can I get the values of lesson and total time in two columns?

Appreciate your help!

Updated query:

SELECT
u.firstname AS FIRST,
u.lastname AS LAST, 
c.fullname AS Course, 
st.attempt AS Attempt,
  (case when st.element = 'cmi.core.total_time' then st.value else NULL end) as timespent,
  (case when st.element = 'cmi.core.lesson_status' then st.value else NULL end) as status

FROM prefix_scorm_scoes_track AS st 
JOIN prefix_user AS u ON st.userid=u.id
JOIN prefix_user_info_data AS uid ON uid.userid = u.id 
JOIN prefix_scorm AS sc ON sc.id=st.scormid
JOIN prefix_course AS c ON c.id=sc.course

 WHERE c.id=1

UNION

SELECT
user2.firstname AS FIRST,
user2.lastname AS LAST,
c.fullname AS Course,
"-" AS Attempt,
"not_started" AS STATUS,
"-" AS DATE

FROM prefix_user_enrolments AS ue
JOIN prefix_enrol AS e ON e.id = ue.enrolid
JOIN prefix_course AS c ON c.id = e.courseid
JOIN prefix_user AS user2 ON user2 .id = ue.userid
JOIN prefix_user_info_data AS uid ON uid.userid = user2.id 
JOIN prefix_scorm AS sc ON sc.course=c.id
LEFT JOIN prefix_scorm_scoes_track AS st ON st.scormid=sc.id AND st.userid=user2.id

WHERE  c.id=1

But then I'm getting the values in multiple rows instead of single row: enter image description here

Cath
  • 125
  • 10
  • Does this answer your question? [MySQL - Rows to Columns](https://stackoverflow.com/questions/1241178/mysql-rows-to-columns) – Jorge Campos May 19 '20 at 16:11
  • im a bit confused by the query, we have users that have rows in _scoes_track that are not related to an enrol(lment), only users that don't have scoes_track have enrol(ment) ? why UNION and not UNION ALL ? ... i understand that this isn't related to the specific question that was asked... and i'm not going to give advice about fixing just one small part of the query when it all looks suspicious. It looks like the UNION got tacked on to it like Red Green had a go at it with duct tape... – spencer7593 May 19 '20 at 17:40
  • even when you had posted aöö ethe data and wanted result see https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query what you want is a pivot table adn are manageable you can see an example [here](https://stackoverflow.com/questions/61413329/mysql-select-group-by-and-transform-rows-to-separate-columns/61416286#61416286) – nbk May 19 '20 at 20:11

0 Answers0