GBase 8a列转行函数unpivot介绍

语法

unpivot 

  [{include_nulls | exclude_nulls}]

  (

    COLUMN | (

     COLUMN[,COLUMN]..

    )

    FOR COLUMN | (

     COLUMN[,COLUMN]..

    )

    IN COLUMN | (

     COLUMN[,COLUMN]...

    ) [ AS LITERAL | (

       LITERAL[,LITERAL]...

      )

    ]

  )

说明

NULLS

• INCLUDE_NULLS: 当要转换的列值为null时，不过滤，结果显示改行
• EXCLUDE_NULLS:当要转换的列值为null时，过滤掉(is not null)，结果也不显示改行。 默认值。

unpivot_COLUMN

新增加的列名，存储

样例

gbase> select * from tpp;

+--------+--------+--------+----------+----------+

| name   | yw_sum | sx_sum | yw_avg   | sx_avg   |

+--------+--------+--------+----------+----------+

| 李四   |    290 |   216 | 145.0000 | 108.0000 |

| 张三   |    240 |   228 | 120.0000 | 114.0000 |

| 王五   |    220 |   272 | 110.0000 | 136.0000 |

+--------+--------+--------+----------+----------+

3 rows in set (Elapsed: 00:00:00.01)

gbase> select * from tpp unpivot(score for class in (yw_sum as '语文总分'));

+----------+--------+----------+--------+--------------+-------+

| yw_avg   | sx_sum | sx_avg   | name   | class        | score |

+----------+--------+----------+--------+--------------+-------+

| 120.0000 |   228 | 114.0000 | 张三   | 语文总分     |   240 |

| 110.0000 |   272 | 136.0000 | 王五   | 语文总分     |   220 |

| 145.0000 |   216 | 108.0000 | 李四   | 语文总分     |   290 |

+----------+--------+----------+--------+--------------+-------+

3 rows in set (Elapsed: 00:00:00.01)

gbase> select * from tpp unpivot(score for class in (yw_sum as '语文总分',sx_sum as '数学总分'));

+----------+----------+--------+--------------+-------+

| yw_avg   | sx_avg   | name   | class        | score |

+----------+----------+--------+--------------+-------+

| 145.0000 | 108.0000 | 李四   | 语文总分     |  290 |

| 145.0000 | 108.0000 | 李四   | 数学总分     |  216 |

| 120.0000 | 114.0000 | 张三   | 语文总分     |  240 |

| 110.0000 | 136.0000 | 王五   | 语文总分     |  220 |

| 120.0000 | 114.0000 | 张三   | 数学总分     |  228 |

| 110.0000 | 136.0000 | 王五   | 数学总分     |  272 |

+----------+----------+--------+--------------+-------+

6 rows in set (Elapsed: 00:00:00.01)

gbase> select * from tpp unpivot(score for class in (yw_sum as '语文总分',sx_sum as '数学总分',yw_avg as '语文平均分'));

+----------+--------+-----------------+----------+

| sx_avg   | name   | class           | score    |

+----------+--------+-----------------+----------+

| 114.0000 | 张三   | 语文总分        | 240.0000 |

| 136.0000 | 王五   | 语文总分        | 220.0000 |

| 114.0000 | 张三   | 数学总分        | 228.0000 |

| 136.0000 | 王五   | 数学总分        | 272.0000 |

| 114.0000 | 张三   | 语文平均分      | 120.0000 |

| 136.0000 | 王五   | 语文平均分      | 110.0000 |

| 108.0000 | 李四   | 语文总分        | 290.0000 |

| 108.0000 | 李四   | 数学总分        | 216.0000 |

| 108.0000 | 李四   | 语文平均分      | 145.0000 |

+----------+--------+-----------------+----------+

9 rows in set (Elapsed: 00:00:00.01)

gbase> select * from tpp unpivot(score for class in (yw_sum as '语文总分',sx_sum as '数学总分',yw_avg as '语文平均分',sx_avg as '数学平均分'));

+--------+-----------------+----------+

| name   | class           | score    |

+--------+-----------------+----------+

| 张三   | 语文总分        | 240.0000 |

| 王五   | 语文总分        | 220.0000 |

| 张三   | 数学总分        | 228.0000 |

| 王五   | 数学总分        | 272.0000 |

| 张三   | 语文平均分      | 120.0000 |

| 王五   | 语文平均分      | 110.0000 |

| 张三   | 数学平均分      | 114.0000 |

| 王五   | 数学平均分      | 136.0000 |

| 李四   | 语文总分        | 290.0000 |

| 李四   | 数学总分        | 216.0000 |

| 李四   | 语文平均分      | 145.0000 |

| 李四   | 数学平均分      | 108.0000 |

+--------+-----------------+----------+

12 rows in set (Elapsed: 00:00:00.01)

gbase>