SQL查询:行到列透视表到PHP数组/HTML输出 [英] SQL Query: Rows to Columns Pivot into PHP Array / HTML Output

问题描述

感谢您在Advance中的所有帮助.

thanks for all your help in Advance.

我在MySQL PHPmyAdmin中有一个名为数据"的表，就像这样；

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

我如何通过最可能的SQL查询或Pivot/php数组输出到表中，以准备好用于HTML表输出；

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

推荐答案

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

• SQLFiddle演示
• SQLFiddle Demo

如果days的数量未知，则首选动态SQL，

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;

• SQLFiddle演示
• SQLFiddle Demo

这篇关于SQL查询:行到列透视表到PHP数组/HTML输出的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！