I have a table like following
TABLE_A
ID PERSON_ID NAME GRADE
---------- ---------- ---------- ----------
1 1 NAME_1 10
2 1 NAME_1 20
3 2 NAME_2 30
4 2 NAME_2 40
...
in this table, for each name there is exactly two rows (two grades). I want to make a query which results like following
RESULT
PERSON_ID NAME GRADE1 GRADE_2
---------- ---------- ---------- ----------
1 NAME_1 10 20
2 NAME_2 30 40
What is the best way for this. I can use self join but I think this is not correct method
I found best answer for PostgreSQL in this link https://www.postgresql.org/docs/9.2/static/tablefunc.html In PostgreSQL there is the crosstab(text)
function.
The crosstab function is used to produce "pivot" displays, wherein data is listed across the page rather than down. For example, we might have data like
row1 val11
row1 val12
row1 val13
...
row2 val21
row2 val22
row2 val23
...
which we wish to display like
row1 val11 val12 val13 ...
row2 val21 val22 val23 ...
...
The
crosstab
function takes a text parameter that is a SQL query producing raw data formatted in the first way, and produces a table formatted in the second way. Thesql
parameter is a SQL statement that produces the source set of data. This statement must return onerow_name
column, onecategory
column, and onevalue
column.N
is an obsolete parameter, ignored if supplied (formerly this had to match the number of output value columns, but now that is determined by the calling query). For example, the provided query might produce a set something like:
row_name cat value
----------+-------+-------
row1 cat1 val1
row1 cat2 val2
row1 cat3 val3
row1 cat4 val4
row2 cat1 val5
row2 cat2 val6
row2 cat3 val7
row2 cat4 val8
The
crosstab
function is declared to returnsetof record
, so the actual names and types of the output columns must be defined in theFROM
clause of the callingSELECT
statement, for example:
SELECT * FROM crosstab('...') AS ct(row_name text, category_1 text, category_2 text);
This example produces a set something like:
<== value columns ==>
row_name category_1 category_2
----------+------------+------------
row1 val1 val2
row2 val5 val6
For more read section F.35.1.2. in URL entered top of the answer
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments