CODEWITHSUNDEEP
sqlpostgresql
Alexander Chandra
Alexander Chandra

Reputation: 659

postgresql transpose with different attribute row

This is the first time i used transpose so i dont know if what i want is possible or not

this is my query

SELECT * 
FROM crosstab( 
    'select p.name, a.attributekey, a.attributevalue
    from productz p 
    join attribute a on a.itemid=p.id
    order by p.name, a.attributekey') 
     AS final_result(name varchar, interface varchar, negativemargin varchar,parity varchar);
select p.name, a.attributekey, a.attributevalue
    from productz p 
    join attribute a on a.itemid=p.id
order by p.name, a.attributekey;

here's the link http://rextester.com/IQNSY51011

but the output is different than what i want, because productz 1 have two row and the productz 2 have 3 row

name      interface negativemargin parity
dufan     true      true           NULL
waterboom android   true           false

the output i want is this below without insert interface,Null to database

name      interface negativemargin parity
dufan     NULL      true           true
waterboom android   true           false

Note: please click "run it" after opening the link

Upvotes: 0

Views: 37

Answers (1)

Nishant Gupta
Nishant Gupta

Reputation: 3656

Solution to your problem:

SELECT * 
FROM crosstab( 
    'select p.name, a.attributekey, a.attributevalue
    from productz p 
    join attribute a on a.itemid=p.id
    order by p.name, a.attributekey',
    'SELECT DISTINCT attributekey FROM attribute ORDER BY 1') 
     AS final_result(name varchar, interface varchar, negativemargin varchar,parity varchar);

LINK: http://rextester.com/OPNK82802

Use crosstab(text, text) with 2 input parameters. The second parameter can be any query that returns one row per attribute matching the order of the column definition at the end.

What is the problem with CROSSTAB(text) i.e. crosstab with 1 parameter?

The main limitation of the single-parameter form of crosstab is that it treats all values in a group alike, inserting each value into the first available column. If you want the value columns to correspond to specific categories of data, and some groups might not have data for some of the categories, that doesn't work well. The two-parameter form of crosstab handles this case by providing an explicit list of the categories corresponding to the output columns.

For more info on crosstab follow the below link:

https://www.postgresql.org/docs/9.2/static/tablefunc.html

Upvotes: 1

Related Questions