Convert multiple columns into one row, and convert multiple columns into one row.

Source: Internet
Author: User

Convert multiple columns into one row, and convert multiple columns into one row.

A friend asked me how to convert multiple columns into one row in Impala. In fact, the built-in functions in Impala can be implemented without using custom functions.

The following is a demonstration:

-Bash-4.1 $ impala-shell

Starting Impala Shell without Kerberos authentication
Connected to cdha: 21000
Server version: impalad version 1.4.2-cdh5 RELEASE (build eac952d4ff674663ec3834778c2b981b252aec78)
Welcome to the Impala shell. Press TAB twice to see a list of available commands.


Copyright (c) 2012 Cloudera, Inc. All rights reserved.


(Shell build version: Impala Shell v1.4.2-cdh5 (eac952d) built on Tue Sep 16 19:15:40 PDT 2014)

[Cdha: 21000]> Create table student (name string, subject string, score decimal (); ------------- create a demo table: student (I have been away from school for four years, but I still don't forget that I am a student ^_^)
Query: create table student (name string, subject string, score decimal (4, 1 ))


Returned 0 row (s) in 1.97 s
[Cdha: 21000]> Insert into student values ('xiaoming', 'Math', 89.5); -------------- insert demo data (Xiao Ming and Hua's learning scores, they have good feelings)
Query: insert into student values ('xiaoming', 'Math', 89.5)
Inserted 1 rows in 2.56 s
[Cdha: 21000]> Insert into student values ('xiaoming', 'inc', 92 );
Query: insert into student values ('xiaoming', 'inc', 92)
Inserted 1 rows in 0.39 s
[Cdha: 21000]> Insert into student values ('xiaoming', 'China', 98 );
Query: insert into student values ('xiaoming', 'China', 98)
Inserted 1 rows in 0.42 s
[Cdha: 21000]> Insert into student values ('shanghai', 'China', 80 );
Query: insert into student values ('shanghai', 'China', 80)
Inserted 1 rows in 0.40 s
[Cdha: 21000]> Insert into student values ('shanghai', 'Math', 89.5 );
Query: insert into student values ('shanghai', 'Math', 89.5)
Inserted 1 rows in 0.29 s
[Cdha: 21000]> Select * from student; ---------- after each test, the teacher is very nervous when reporting the score
Query: select * from student
+ ---------- + --------- + ------- +
| Name | subject | score |
+ ---------- + --------- + ------- +
| Xiaoming | english | 92.0 |
| Huahua | chinese | 80.0 |
| Xiaoming | China | 98.0 |
| Huahua | math | 89.5 |
| Xiaoming | math | 89.5 |
+ ---------- + --------- + ------- +
Returned 5 row (s) in 0.23 s


[Cdha: 21000]> Select name, group_concat (subject, ',') from student group by name; ------------ let's see it. Splice multiple columns together.
Query: select name, group_concat (subject, ',') from student group by name
+ ---------- + ---------------------------- +
| Name | group_concat (subject, ',') |
+ ---------- + ---------------------------- +
| Xiaoming | english, chinese, math |
| Huahua | chinese, math |
+ ---------- + ---------------------------- +
Returned 2 row (s) in 0.38 s

------------ The following is a valuable example to show the tricks of experts.

------------ In this way, you can see the scores of Xiao Ming and Hua's subjects on a line.

[Cdha: 21000]> Select name, group_concat (concat_ws ('=', subject, cast (score as string), ',') from student group by name;
Query: select name, group_concat (concat_ws ('=', subject, cast (score as string), ',') from student group by name
+ ---------- + ------------------------------------------------------------------- +
| Name | group_concat (concat_ws ('=', subject, cast (score as string), ',') |
+ ---------- + ------------------------------------------------------------------- +
| Xiaoming | english = 92.0, chinese = 98.0, math = 89.5|
| Huahua | chinese = 80.0, math = 89.5|
+ ---------- + ------------------------------------------------------------------- +
Returned 2 row (s) in 0.39 s
[Cdha: 21000]>


Hive column and column conversion see: http://blog.csdn.net/jiangshouzhuang/article/details/46810529

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.