通过sql将表中的行数据转换成列数据来进行存储,对于一些定长的可以通过case来做,对于一些不定长的,可以使用以下的sql来处理。
SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'Max(IF(a.data_name = ''',
a.data_name,
''', a.data_value, 0)) AS ''',
a.data_name, ''''
)
) INTO @sql
FROM cps_data_collection_detail a;
SET @sql = CONCAT('Select a.main_id,', @sql,
' From cps_data_collection_detail a Group by a.main_id' );
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;