up vote 0 down vote favorite
1

thanks for all your help in Advance.

I have a table called 'data' in MySQL PHPmyAdmin, like this;

Primary | Date| User | Response
   1      Mon   Tom      Yes
   2      Mon   Terry    No
   3      Mon   Lucas    Yes
   4      Tue   Tom      No
   5      Tue   Terry    No
   6      Tue   Lucas    Yes
   7      Wed   Tom      Yes
   8      Wed   Terry    Yes
   9      Wed   Lucas    No

How do i output into a table, via most probably SQL query or Pivot / php arrays, into this ready for HTML table output;

Date | Tom | Terry | Lucas
Mon    Yes   No      Yes
Tue    No    No      Yes
Wed    Yes   Yes     No
share|improve this question
up vote 1 down vote 
SELECT  Date,
        MAX(CASE WHEN user = 'Tom' THEN Response ELSE NULL END) Tom,
        MAX(CASE WHEN user = 'Terry' THEN Response ELSE NULL END) Terry ,
        MAX(CASE WHEN user = 'Lucas' THEN Response ELSE NULL END) Lucas
FROM    tableName
GROUP   BY Date

if you have unknown number of days, a dynamic sql is more preferred,

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(case when user = ''',
      user,
      ''' then Response end) AS ',
      user
    )
  ) INTO @sql
FROM tableName;

SET @sql = CONCAT('SELECT  Date, ', @sql, ' 
                  FROM    tableName
                  GROUP   BY Date');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
share|improve this answer
    
+1 its still strange query :) – Azad Feb 22 '13 at 16:02

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.