Align populated values by columns using case when in mysql

Asked

Viewed 31 times

0

As I could align the values in this SQL query per column, are 2 possible questions for each question in a list of several users who answered the first and second question:

  • question one? answer a
  • question two? answer two

This is the SQL extraction of these answers today:

SELECT 
         evuser.usr_id as user_id,        
                (case when (quest.evl_id = 4 and quest.evq_id = 9) then evq_question_name end) as pergunta1,  
                (case when (quest.evl_id = 4 and quest.evq_id = 10) then evq_question_name end) as pergunta2,  
                (case when eua.evq_id = 9 then eua_answer end) as resposta1,
                (case when eua.evq_id = 10 then eua_answer end) as resposta2    
        from evaluation_user_answers eua
        left join evaluation_user_applied evuser
            on eua.eua_id = evuser.eua_id
        left join evaluation_questions quest
            on quest.evq_id = eua.evq_id    
        where 
            eua.evq_id in (9, 10) and quest.evl_id=4;

Tables:

CREATE TABLE `evaluation_questions` (
  `evq_id` int(11) NOT NULL AUTO_INCREMENT,
  `evl_id` int(3) NOT NULL,
  `evq_question_name` varchar(100) DEFAULT NULL,
  `evl_type` int(1) DEFAULT NULL,
  `evl_order` int(1) DEFAULT NULL,
  `evl_max_pontuation` int(2) DEFAULT NULL,
  `evl_required` bit(1) DEFAULT NULL,
  `evl_created` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `evl_updated` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`evq_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

CREATE TABLE `evaluation_user_applied` (
  `eua_id` int(11) NOT NULL AUTO_INCREMENT,
  `evp_id` int(11) NOT NULL,
  `usr_id` int(11) NOT NULL,
  `eua_status` tinyint(1) DEFAULT NULL,
  `eua_created` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `eua_updated` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`eua_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

CREATE TABLE `evaluation_user_answers` (
  `eua_id` int(11) NOT NULL,
  `evq_id` int(11) NOT NULL,
  `eua_answer` varchar(300) DEFAULT NULL,
  `eua_created` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `eua_updated` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

See that if I catch group 9 he brings me like this:

inserir a descrição da imagem aqui

If I put the group 10, like this:
inserir a descrição da imagem aqui

But if I put the two together, you’re coming up empty, I’d like to get everything lined up in the columns:

inserir a descrição da imagem aqui

  • See if in this example created would be a valid answer to your question, if I understood correctly, could solve without using the CASE WHEN. See example http://sqlfiddle.com/#! 9/948113/11.

  • Using CASE WHEN, could look like this http://sqlfiddle.com/#! 9/948113/14?

No answers

Browser other questions tagged

You are not signed in. Login or sign up in order to post.