mysql convert rows to cols from one table dynamic

Question

I have table has data as

id | qs | ans         
=============
1  | qs1|ans1 
1  | qs2|ans2

I need to get data from this table as

id | qs1 | qs2 
1  | ans1| ans2

note I don't know how much rows will be with the same id and I don't know the data in qs cell so I cant use case my data is text, not a number

Possible duplicate of https://stackoverflow.com/questions/1241178/mysql-rows-to-columns — Bill Karwin, Dec 21 '17 at 23:33
`so I cant use case` that's very unfortunate because there is no alternative in MySQL (so far). — Paul Maxwell, Dec 22 '17 at 00:23
yes, you are right but what I mean the qs cols may have different data some time number some time text for that I mean that I cant use code with case like case(color then ) it should be case(? then ) — Hakam Karawi, Dec 22 '17 at 11:52
Possible duplicate of [MySQL - Rows to Columns](https://stackoverflow.com/questions/1241178/mysql-rows-to-columns) — Amit Kumar, Dec 22 '17 at 12:14
@HakamKarawi Why do you want to read the data from the database this way? Why not read it in the "normal" way? — Progman, Dec 22 '17 at 15:36
@Progman this data survey results and to analyze this data every survey should be in one row — Hakam Karawi, Dec 22 '17 at 19:59

score 1 · Accepted Answer · answered Dec 24 '17 at 02:41

CREATE TABLE qanda
    (`id` int, `qs` varchar(100), `ans` varchar(100));

INSERT INTO qanda
    (`id`, `qs`, `ans`)
VALUES
    (1, 'qs1', 'ans1'),
    (1, 'qs2', 'ans2'),
    (1, 'qs3', 'ans3'),
    (1, 'qs4', 'ans4'),
    (1, 'qs5', 'ans4'),
    (2, 'qs1', 'ans1'),
    (2, 'qs2', 'ans2'),
    (2, 'qs3', 'ans3'),
    (2, 'qs4', 'ans4');

SELECT GROUP_CONCAT('max(IF(qs=\'',qs,'\', ans, NULL)) AS `',`qs`,'`')
into @query
FROM qanda
where id = 1;

SET @query := concat('select id,',@query,' from qanda where id = 1 group by id');
PREPARE dynamic_statement FROM @query;
EXECUTE dynamic_statement;
DEALLOCATE PREPARE dynamic_statement;

id | qs1  | qs2  | qs3  | qs4  | qs5 
-: | :--- | :--- | :--- | :--- | :---
 1 | ans1 | ans2 | ans3 | ans4 | ans4

dbfiddle demo here

mysql convert rows to cols from one table dynamic

1 Answers1