asked on

Get columns from query

I have already asked a similar question under Q_28629062 however trying to get a similar solution to work at the moment.

I have a table of survey answers:-

And Im trying to get the table:-

So Im using the following SQL statment:-

SELECT distinct Table1.surveyID, Table1.question
FROM Table1
GROUP BY Table1.surveyID, Table1.question
HAVING (((Table1.surveyID)=1))
ORDER BY Table1.question;

Open in new window

To build up the query:-

SELECT surveyID, responseID, 
          (case when `question`= 'Q1' then `response` else '' end) as `Q1`,
          (case when `question`= 'Q2' then `response` else '' end) as `Q2`,
          (case when `question`= 'Q3' then `response` else '' end) as `Q3`
FROM Table1
GROUP BY surveyID, responseID
HAVING (((Table1.surveyID)=1));

Open in new window

However the result is:-

I was going to use SQLite, however think it is a bit underpowered to do this type of query so thinking of using MySQL instead now.

I'm not too sure how I can group the responseID together now, does anyone have any pointers?

Mark Bullock

Here's a way to get Q1. You can add the other questions. I wasn't able to test it.
select t2.surveyId, t2.responseId
, (select tq1.response from table1 as tq1 where tq1.surveyId=t1.surveyId and tq1.responseId=t1.responseId and t1.question='Q1') as Q1
from table1 as t1, (select distinct surveyId, responseId from Table1) as t2
where t1.surveyId=t2.surveyId and t1.responseId=t2.responseId

ASKER CERTIFIED SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

NerdsOfTech

You can simplify the work by running a script using a scripting language like PHP, that is built to process data, and output your desired format very easily:

1. connect to database
2. run the simple query (initial table)
3. put and sort results into an array of your desired format
4. output your results (emulate final table)