mysql实现数据转置

[benpao002]

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怎么写呢

[西红柿ki]

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;

[crynono]

用高级语言来写吧，sql语句写起来麻烦，就算写了效率也很低

[老马历写记]

同2楼，高级语言写吧

[benpao002]

怎么用高级语言呢，得在hive里实现