data_source id_cnt poi_code_cnt country_cnt country_id_cnt province_cnt
J 16541587 16541528 16541587 16541587 14744184
G 38621791 38621791 38621791 38621791 38621791
G_T 38621791 38621791 38621791 38621791 38621791
T_T 55163378 55163378 55163378 55163378 55163378
J_T 16541587 16541587 16541587 16541587 16541587
filed_name J G G_T T_T J_T
id_cnt 16541587 38621791 38621791 55163378 16541587
poi_code_cnt 16541528 38621791 38621791 55163378 16541587
country_cnt 16541587 38621791 38621791 55163378 16541587
country_id_cnt 16541587 38621791 38621791 55163378 16541587
province_cnt 14744184 38621791 38621791 55163378 16541587
想把上面的数据通过mysql转置为下面这样,mysql怎么写呢
J 16541587 16541528 16541587 16541587 14744184
G 38621791 38621791 38621791 38621791 38621791
G_T 38621791 38621791 38621791 38621791 38621791
T_T 55163378 55163378 55163378 55163378 55163378
J_T 16541587 16541587 16541587 16541587 16541587
filed_name J G G_T T_T J_T
id_cnt 16541587 38621791 38621791 55163378 16541587
poi_code_cnt 16541528 38621791 38621791 55163378 16541587
country_cnt 16541587 38621791 38621791 55163378 16541587
country_id_cnt 16541587 38621791 38621791 55163378 16541587
province_cnt 14744184 38621791 38621791 55163378 16541587
想把上面的数据通过mysql转置为下面这样,mysql怎么写呢
SELECT 'id_cnt' AS filed_name,
MAX(CASE data_source WHEN 'J' THEN id_cnt END) AS J,
MAX(CASE data_source WHEN 'G' THEN id_cnt END) AS G,
MAX(CASE data_source WHEN 'G_T' THEN id_cnt END) AS G_T,
MAX(CASE data_source WHEN 'T_T' THEN id_cnt END) AS T_T,
MAX(CASE data_source WHEN 'J_T' THEN id_cnt END) AS J_T
FROM TEST1
UNION
SELECT 'poi_code_cnt' AS filed_name,
MAX(CASE data_source WHEN 'J' THEN poi_code_cnt END) AS J,
MAX(CASE data_source WHEN 'G' THEN poi_code_cnt END) AS G,
MAX(CASE data_source WHEN 'G_T' THEN poi_code_cnt END) AS G_T,
MAX(CASE data_source WHEN 'T_T' THEN poi_code_cnt END) AS T_T,
MAX(CASE data_source WHEN 'J_T' THEN poi_code_cnt END) AS J_T
FROM TEST1
UNION
SELECT 'country_cnt' AS filed_name,
MAX(CASE data_source WHEN 'J' THEN country_cnt END) AS J,
MAX(CASE data_source WHEN 'G' THEN country_cnt END) AS G,
MAX(CASE data_source WHEN 'G_T' THEN country_cnt END) AS G_T,
MAX(CASE data_source WHEN 'T_T' THEN country_cnt END) AS T_T,
MAX(CASE data_source WHEN 'J_T' THEN country_cnt END) AS J_T
FROM TEST1
UNION
SELECT 'country_id_cnt' AS filed_name,
MAX(CASE data_source WHEN 'J' THEN country_id_cnt END) AS J,
MAX(CASE data_source WHEN 'G' THEN country_id_cnt END) AS G,
MAX(CASE data_source WHEN 'G_T' THEN country_id_cnt END) AS G_T,
MAX(CASE data_source WHEN 'T_T' THEN country_id_cnt END) AS T_T,
MAX(CASE data_source WHEN 'J_T' THEN country_id_cnt END) AS J_T
FROM TEST1
UNION
SELECT 'province_cnt' AS filed_name,
MAX(CASE data_source WHEN 'J' THEN province_cnt END) AS J,
MAX(CASE data_source WHEN 'G' THEN province_cnt END) AS G,
MAX(CASE data_source WHEN 'G_T' THEN province_cnt END) AS G_T,
MAX(CASE data_source WHEN 'T_T' THEN province_cnt END) AS T_T,
MAX(CASE data_source WHEN 'J_T' THEN province_cnt END) AS J_T
FROM TEST1;