Implementation of 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  | 中文版 |   Lucy  | Physics |    Lucy  | Math    |    Lily  | 中文版 |    Lily  | Physics |    Bayi Lily  | Math    |    David | 中文版 |   David | Physics |    David | Math    |    The-the-Simon | 中文版 |    Simon | Physics |    Simon | Math    |    (rows) time:2.066 ms

You want to achieve the following results:

Name  | 中文版 | Physics | Math-------+---------+---------+------Simon |      |      |   Lucy  |     |      |   Lily  |      |      Bayi |   David |     |      |   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 if 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  | 中文版 | Physics | Math-------+---------+---------+------Simon |      |      |   Lucy  |     |      |   Lily  |      |      Bayi |   David |     |      |   Time:1.123 MS (4 rows)

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 |      |      |   Lucy  |     |      |   Lily  |      |      Bayi |   David |     |      |   time:2.059 MS (4 rows)

3, using PostgreSQL's own aggregation function to achieve

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 the score group by name order by name Desct_girl (#) as T; Name  | 中文版 | Physics | Math-------+---------+---------+------Simon | | | | | |-| | |      --|--| | | | s) 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 NA Me,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  | 中文版 | physics | math-------+---------+---------+------Simon | 90< c5/>| About | | | | $  | |      ---|---| | | e:2.687 ms

5, with Plpgsql to achieve

t_girl=# Create type Ytt_crosstab as (name text, 中文版 text, Physics text, Math text); CREATE typetime:22.518 mscreate or Replace function Func_ytt_crosstab () returns SETOF Ytt_crosstabas $ytt $ declare V_nam                E 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 the score group by name order by name Desc;begin open CS1;    Loop fetch CS1 into V_name,v_tmp_result;    Exit when not found;    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 | 中文版 | Physics | Math-------+---------+---------+------Simon | 90 | 76 | Lucy | 100 | 90      | Lily | 95 | 81 | David | 100 | 86 | time:2.127 MS (4 rows)

Implementation of PostgreSQL Cross table