PostgreSQL Row to Column

Q: How do I page && conditionally && by order && name do not duplicate data?

A: In fact, is the row to the column, then, PostgreSQL how to go to the column, Baidu, there are about three kinds of wording

Notation 1 GROUP by + sum + case when

Select name,
Sum(case when zbfm=‘age ‘then value else 0 end) as age,
Sum(case when zbfm=‘height ‘then value else 0 end) as height,
Sum(case when zbfm=‘weight ‘then value else 0 end) as weight
From test group by name
Having name like ‘%1‘ and length(name)=4 order by age desc 

Notation 2 using the crosstab crossover function of PostgreSQL

Crosstab (unknown, unknown) does not exist

Select * from
Crosstab(
‘select name,zbfm,value from test where name like ‘‘%1‘‘ and length(name)=4’,$$values(‘age’), (‘height’), (‘weight’)$$)
As score(name text, age int, height int, weight int)
Order by age desc 

Notation 3group by + String_agg + split_part (group, row to column, character cut)

Select name,
Split_part(split_part(temp,‘,‘,1),‘:‘,2) as age,
Split_part(split_part(temp,‘,‘,2),‘:‘,2) as height,
Split_part(split_part(temp,‘,‘,3),‘:‘,2) as weight
From(
Select name, string_agg(zbfm||‘:‘||value,‘,‘) as temp from test
Group by name
Having name like ‘%1‘ and length(name)=4
) as t order by age desc 

GROUP BY + String_agg

 
select name, string_agg(zbfm||‘:‘||value,‘,‘) from test
group by name 
having name like ‘%1‘ and length(name)=4

Other Build Table statements

 
CREATE TABLE test
(
  id serial NOT NULL,
  value integer,
  name character varying,
  zbfm character varying,
  CONSTRAINT pkey PRIMARY KEY (id)
)

Inserting data (Python)

Import psycopg2
From random import random
Conn = psycopg2.connect(database="postgres", user="postgres", password="password", host="ip", port="port")
Cur = conn.cursor()

Def insertData():
     Names = [‘passer’s ‘, ‘Wang Nima’, ‘Tang Maru’’
     Zbfms = [‘age’, ‘height’, ‘weight’’

     For i in range(100):
         Sqlstr = ‘insert into test(name, zbfm, value) values‘
         For j in range(100):
             For name in names:
                 For zbfm in zbfms:
                     Sqlstr += "(‘%s‘,‘%s‘,%d),"%(name+str(i*100+j),zbfm,int(100*random()))
         Cur.execute(sqlstr[:-1])
         Conn.commit()
         Print(i)

If __name__ == ___main__‘:
     insertData()
     selectData() 

Reference

Five ways PostgreSQL implements crosstab (row-and-column conversions)

PostgreSQL Row to Column