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?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mark BullockQA EngineerCommented:
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
PortletPaulEE Topic AdvisorCommented:
What you are seeking is known as a "pivot query" and you are very close indeed to solving this yourself.

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

Please do not worry that the MAX() function will somehow corrupt the outcome. If you ispect the sample data there is   only one row for each combination of ( surveyID, responseID, question ) so MAX() simply locates that single value.

The HAVING clause is for conditions that can only be tested AFTER the group by claue. For example
          HAVING COUNT(*) > 1
          because that count requires the GROUP BY
Use the WHERE clause for basic data conditions

In case a source record is missing, and you must have empty strings instead of NULLs then use IFNULL() like this

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
NerdsOfTechTechnology ScientistCommented:
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)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.