MySQL – Rows to Columns

MySQL

I have a table with 3 columns, participant_id, question, answer. for each participant, there will be five question.

How do fetch a report like against each participant each question as a column header and answer as value. So in report for each participant there will be one entry in report?

expected output:
group using participant_id and each question should be the column header and custtusponding answer will be the column value.

Best Answer

This is similar to pivot table , Try This

create view pivot_questions as (
  select
    questions.*,
    case when question = "question1" then itemvalue end as question1,
    case when itemname = "question2" then itemvalue end as question2,
    case when itemname = "question3" then itemvalue end as question3,
    case when itemname = "question4" then itemvalue end as question4,
    case when itemname = "question5" then itemvalue end as question5
  from questions
);

select * from pivot_questions;

Related Solutions

MySQL Database Design – Elegant Solutions

Your usernames are to be unique. Good, they should be. But now you seem to think that Username would make a good primary key. Not necessarily. One important characteristic of a PK is stability. Unless you wish to implement a "one you've chosen a username you're stuck with it forever" policy (and that is a decidedly user-unfriendly policy), someone may want to change their username. Doing so when that username is scattered all over the database is going to be difficult.

I am not a fan of design rules that demand a surrogate key for every table. But typically 50% or more of tables should have surrogate keys. This is one of them.

And I certainly hope that when you say the login table will contain the password, you do mean the hash of the password, don't you?

@ypercube speaks for all of us about the "for each question, there will be separate table" idea. Not good.

Now as for the user's code. For that you have choices. You don't mention the target DBMS (if you even have one at this point) but some handle text or clob fields better than others. The question of handling actual disk files is one you will have to research yourself. Will those files reside in a file server, within a version control system, just a dedicated folder on some disk somewhere? In any case, what are the OS-based and/or network access privileges that will be needed? You get the idea. There are a lot of questions unrelated to databases that must be answered.

Not to say that one is superior to the other, but unless you or someone on your team has done this before, you may want to start with storing the code in the database. That will probably get you started faster and you can move to system files (if you determine it to be the better option) more or less at your leisure.

MySQL: SELECT with SUM() and COUNT() as new columns

If we assume that 'QtyOfParts' and 'FamilyID' are existing columns in the table and furthermore, that the other 2 columns are "calculated", then maybe the following query would be helpful (creating some test data first):

create table qf (
  QtyOfParts integer
, FamilyID integer 
);

insert into qf values 
(28, 1), (32, 1), (19, 1), 
(28, 2), (38, 2), (46, 3),
(30, 3), (51, 3), (24, 3);

Thus:

mysql> select * from qf;
+------------+----------+
| QtyOfParts | FamilyID |
+------------+----------+
|         28 |        1 |
|         32 |        1 |
|         19 |        1 |
|         28 |        2 |
|         38 |        2 |
|         46 |        3 |
|         30 |        3 |
|         51 |        3 |
|         24 |        3 |
+------------+----------+
9 rows in set (0.00 sec)

Suggested query:

mysql> select 
    ->   t1.qtyofparts
    -> , t1.familyid
    -> , t2.nbr NumInFamily
    -> , t2.sum FullQtyOfParts 
    -> from qf t1 join ( 
    ->     select 
    ->       familyid
    ->     , count(familyid) nbr
    ->     , sum(qtyofparts) sum
    ->     from qf 
    ->     group by familyid ) t2 on t1.familyid = t2.familyid
    -> ;
+------------+----------+-------------+----------------+
| qtyofparts | familyid | NumInFamily | FullQtyOfParts |
+------------+----------+-------------+----------------+
|         28 |        1 |           3 |             79 |
|         32 |        1 |           3 |             79 |
|         19 |        1 |           3 |             79 |
|         28 |        2 |           2 |             66 |
|         38 |        2 |           2 |             66 |
|         46 |        3 |           4 |            151 |
|         30 |        3 |           4 |            151 |
|         51 |        3 |           4 |            151 |
|         24 |        3 |           4 |            151 |
+------------+----------+-------------+----------------+
9 rows in set (0.00 sec)

Best Answer

Related Solutions

MySQL Database Design – Elegant Solutions

MySQL: SELECT with SUM() and COUNT() as new columns

Related Question