基于星期几列的Pivot MySQL表

Have the data in this format in db

在db中以这种格式存储数据

---------------------------------------------
count | margin |    date    | week | day    |
---------------------------------------------
 230  |   140  | 2013-01-01 |  0   | Monday  |
----------------------------------------------
 500  |   340  | 2013-01-02 |  0   | Tuesday  |
----------------------------------------------
 200  |   240  | 2013-01-08 |  1   | Monday  |
----------------------------------------------

require out put is

要求外出是

-----------------------------------------------------------
|   week  |   monday  |  tuesday  |  wednesday  | .........
-----------------------------------------------------------
|    0    |  230:140  |  500:340  |
-----------------------------------------------------------   
|    1    |  200:240  | 
-----------------------------------------------------------

any help is appreciated

任何帮助表示赞赏

1 个解决方案

#1

Try this static query:

试试这个静态查询:

SELECT Week
    ,GROUP_CONCAT(CASE WHEN day = 'Monday' 
                       THEN CONCAT(count,':',margin) ELSE NULL END) AS Monday
    ,GROUP_CONCAT(CASE WHEN day = 'Tuesday' 
                       THEN CONCAT(count,':',margin) ELSE NULL END) AS Tuesday
    ,GROUP_CONCAT(CASE WHEN day = 'Wednesday' 
                       THEN CONCAT(count,':',margin) ELSE NULL END) AS Wednesday
    ,GROUP_CONCAT(CASE WHEN day = 'Thursday' 
                       THEN CONCAT(count,':',margin) ELSE NULL END) AS Thursday
    ,GROUP_CONCAT(CASE WHEN day = 'Friday' 
                       THEN CONCAT(count,':',margin) ELSE NULL END) AS Friday
    ,GROUP_CONCAT(CASE WHEN day = 'Saturday' 
                       THEN CONCAT(count,':',margin) ELSE NULL END) AS Saturday
    ,GROUP_CONCAT(CASE WHEN day = 'Sunday' 
                       THEN CONCAT(count,':',margin) ELSE NULL END) AS Sunday
FROM MyTable 
GROUP BY Week

You can also use this dynamic query:

您还可以使用此动态查询:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'GROUP_CONCAT(CASE WHEN `day` = ''',
      `day`,
      ''' THEN CONCAT(count,'':'',margin) ELSE NULL END) AS `',
      `day`, '`'
    )
  ) INTO @sql
FROM MyTable;

SET @sql = CONCAT('SELECT Week, ', @sql,'
                     FROM MyTable
                    GROUP BY Week
                  ');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

See this SQLFiddle