logo-MindMajix
A
Aryan Sharma

Posted on 21st June 2024|56 views

0
votes

Postgresql Pivot

How to create a pivot table in the PostgreSQL?

1 answers
Answers
P
veerubhotla sowmithri

Posted on 21st June 2024| views

  • As compared to the cross tab() function capabilities, we can say that the Pivot table is far more efficient.
  • The Pivot table used for the data processing, and it has a lot of advantages because DB forges studio for PostgreSQL automatically calculates and generates an average of values, maximum value, minimum value, count of values and standard deviation for all the columns and rows. Which is quite beneficial for Pivot tables.
  • This table below gives a list of values that are not clear and lengthy, but the Pivot table can change this into a simple form as follows.

Raw table

Student

Subject

Evaluation_result

Smith, John

Music

7.0

Smith, John

Maths

4.0

Smith, John

History

9.0

Smith, John

Language

7.0

Smith, John

Geography

9.0

Gabriel, Peter

Music

2.0

Gabriel, Peter

Maths

10.0

Gabriel, Peter

History

7.0

Gabriel, Peter

Language

4.0

Gabriel, Peter

Geography

10.0

 

Pivot table

 

Student

Geography 

History

Language

Maths

Music

Gabriel, Peter

10.0

7.0

4.0

10.0

2.0

Smith, John

9.0

9.0

7.0

4.0

7.0

  • The Pivot table is useful to give data such as averages, fill incomplete data so on.

Syntax:

SELECT student, subject FROM evaluations ORDER BY 1,2

AS final_result(Student TEXT, Geography NUMERIC,History NUMERIC,Language NUMERIC,Maths NUMERIC,Music NUMERIC)

 

SELECT *

FROM crosstab( 'select student, subject from evaluations order by 1,2')

     AS final_result(Student TEXT, Geography NUMERIC,History NUMERIC,Language NUMERIC,Maths NUMERIC,Music NUMERIC);
 

  • By using the above syntax, we can create a Pivot table, and it automatically updates the average of values, maximum value, minimum value, count of values and standard deviation and many more in column format as well as row format.

Write your answer

STILL GOT QUERIES?

Get a Live FREE Demo

  • Explore the trending and niche courses and learning maps
  • Learn about tuition fee, payment plans, and scholarships
  • Get access to webinars and self-paced learning videos
Name
Course *
Email *
Phone Number