Mysql data transpose-CodePudding

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
38621791 38621791 38621791 38621791 38621791 G_T
T_T 55163378 55163378 55163378 55163378 55163378
J_T 16541587 16541587 16541587 16541587 16541587

Filed_name J G G_T T_T J_T
16541587 38621791 38621791 55163378 16541587 id_cnt
16541528 38621791 38621791 55163378 16541587 poi_code_cnt
16541587 38621791 38621791 55163378 16541587 country_cnt
16541587 38621791 38621791 55163378 16541587 country_id_cnt
14744184 38621791 38621791 55163378 16541587 province_cnt

Want to put the above data by mysql transposed as follows, mysql how to write

CodePudding user response:

 
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 
The FROM TEST1 
The 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 
The FROM TEST1 
The 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 
The FROM TEST1 
The 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 
The FROM TEST1 
The 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 
The FROM TEST1.

CodePudding user response:

In a high-level language to write, SQL statements to write up trouble, even if writing efficiency is low

CodePudding user response:

With the second floor, high-level language to write

CodePudding user response:

How in a high-level language, to implement in the hive