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
2
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
#1
2
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;