CODEWITHSUNDEEP
sqlpostgresqlpivot
MoienGK
MoienGK

Reputation: 4654

What Is The SQL Query For Creating a Pivot Table?

i am trying to create a SQL query in order to create a pivot table, and my data is on a PostgreSQL database.

as i understand there are 4 elements involved in creating a pivot table

1) row group

2) column group

3) measure group

4) a function (e.g : Max)

and i think CASE ... WHEN columns are playing a role in creating a pivot (crosstab) query.

so with these elements, what the corresponding SQL Query will look like?

Upvotes: 0

Views: 114

Answers (1)

Taryn
Taryn

Reputation: 247840

If you want to convert row data into columns, then you can use an aggregate function along with a CASE expression.

Let's say your data is similar to the following:

create table yourtable
(
  id int,
  name varchar(50),
  amount decimal (10,2)
);

insert into yourtable
select 1, 'Jim', 50.00 union all
select 2, 'Bob', 150.00 union all
select 3, 'Blah', 500.00;

And you want to convert the names into columns, then you could use a basic query similar to the following to get the result:

select
  sum(case when name = 'Jim' then amount else 0 end) as Jim,
  sum(case when name = 'Bob' then amount else 0 end) as Bob,
  sum(case when name = 'Blah' then amount else 0 end) as Blah
from yourtable

This would give you a result of:

| Jim    | Bob    | Blah   |
| 50.00  | 150.00 | 500.00 |

Upvotes: 2

Related Questions