Realization of "original" PostgreSQL cross-table

Here I would like to demonstrate how to achieve the cross-table in PostgreSQL display, as to what is a cross-table, I will not say, the degree Niang go oh.

The original table data is as follows:

t_girl=# select * from score; name  | subject | score - ------+---------+------- Lucy  | English |   100 Lucy   | physics |    90 lucy  | math    |     85 lily  | english |    95 lily   | Physics |    81 Lily  | Math     |    84 david | english |   100 david |  Physics |    86 David | Math    |     89 Simon | English |    90 Simon |  physics |    76 simon | math    |     79 (12 rows) time:  2.066 ms 

You want to achieve the following results:

name | 中文版 | Physics |      Math-------+---------+---------+------Simon |      90 |   76 |     Lucy |      100 |   90 |      Lily |      95 |   81 |     David |      100 |   86 | 89

There are several ways to do this:

1. Show it with standard SQL

T_girl=# select name, t_girl-#  sum (case when subject =  ' 中文版 ')  then score else 0 end)  as  "中文版", T_girl-#  sum (Case when  subject =  ' Physics '  then  score else 0 end)  as  " Physics ", T_girl-#  sum (case when subject =  ' Math '    then  Score else 0 end)  as  "Math"  t_girl-#  from scoret_girl-#   group by name order by name desc; name  | english  | Physics | Math -------+---------+---------+------ Simon |       90 |      76 |   79 lucy   |     100 |      90 |    85 lily  |      95 |      81 |   84  David |     100 |      86 |    89 (4 rows) time: 1.123 ms

2. Function implementation with the third-party extension tablefunc provided by PostgreSQL

The following function crosstab SQL must have three fields, name, classification, and classification values as starting parameters, which must be in the name, categorical values as output parameters.

t_girl=# Select *from crosstab (' Select Name,subject,score from score ORDER BY name Desc ', $ $values (' 中文版 ':: Text), (' Phy Sics ':: Text), (' Math ':: Text) $$) as score (name text, 中文版 int, Physics int, Math int); name | 中文版 | Physics |      Math-------+---------+---------+------Simon |      90 |   76 |     Lucy |      100 |   90 |      Lily |      95 |   81 |     David |      100 |   86 | time:2.059 MS (4 rows)

3, implemented with PostgreSQL's own aggregate function

T_girl=# select name,split_part (Split_part (TMP, ', ', 1), ': ', 2)  as  "中文版",t_girl-#  Split_part (Split_part (TMP, ', ', 2), ': ', 2)  as  "Physics", T_girl-# split_part (Split_part (TMP, ', ', 3), ': ', 2)  as  "Math" t_girl-# fromt_girl-#  (T_girl (# select name,string_agg (subject| | ') : ' | | Score, ', ')  as tmp from score group by name order by name  desct_girl (# )  as T; name  | English | Physics |  math -------+---------+---------+------ simon | 90      |  76      | 79 Lucy  | 100      | 90      | 85 Lily  | 95       | 81      | 84 David | 100      | 86      | 89 (4 rows) time: 2.396 ms 

4. Storage function Implementation

create or replace function func_ytt_crosstab_py  () Returns setof ytt_ crosstabas  $ytt $  for row in plpy.cursor ("Select name,string_agg (subject| | : ' | | Score, ', ')  as tmp from score group by name order by name  desc "):       a = row[' tmp '].split (', ')        yield  (row[' name '],a[0].split (': ') [1],a[1].split (': ') [1],a[2].split (': ') [1]) $ytt $  Language plpythonu;t_girl=# select name,english,physics,math from  func_ytt_ Crosstab_py (); name  | english | physics | math -------+--------- +---------+------ Simon | 90      | 76       | 79 Lucy  | 100     | 90       | 85 Lily  | 95      | 81       | 84 David | 100     | 86       | 89 (4 rows) time: 2.687 ms

5, with Plpgsql to achieve

t_girl=# create type ytt_crosstab as  (name text, english text,  Physics text, math text); Create typetime: 22.518 mscreate or replace function func_ytt_crosstab   () returns setof ytt_crosstabas  $ytt $  declare v_name text :=   ';                 v_ english text :=  ';v_physics text :=  ';v_math text :=  '; v_tmp_ result text :=  ';   declare cs1 cursor for select name, String_agg (subject| | ': ' | | Score, ', ')  from score group by name order by name desc;begin   open cs1;  loop    fetch cs1 into v_name,v_tmp_ Result;    exit when not found;  &nbSp; v_english = split_part (Split_part (V_tmp_result, ', ', 1), ': ', 2);     v_ Physics = split_part (Split_part (V_tmp_result, ', ', 2), ': ', 2);     v_math =  split_part (Split_part (V_tmp_result, ', ', 3), ': ', 2);     return query select  v_name,v_english,v_physics,v_math;  end loop;end; $ytt $ language plpgsql;t_girl= # select name,english,physics,math from func_ytt_crosstab (); name  |  english | physics | math -------+---------+---------+------ simon | 90       | 76      | 79 Lucy   | 100     | 90      | 85 lily   | 95      | 81      |  84 david | 100     | 86      | 89 (4 rows) Time:  2.127 ms

This article is from "God, we don't see!" "Blog, be sure to keep this provenance http://yueliangdao0608.blog.51cto.com/397025/1582306

Realization of "original" PostgreSQL cross-table