I also added the in clause which is not optional for pivot. (Note: As before I do not have a copy of 11g to test this on so I have not verified its functionality)Įdit2: Changed v in the pivot and pivot xml statements to max(v) since it is supposed to be aggregated as mentioned in one of the comments. (see the XML TYPE section near the bottom of the page located at ) select * from (Note: I do not have a copy of 11g to test this on so I have not verified its functionality)ĮDIT - pivot xml option (also Oracle 11g)Īpparently there is also a pivot xml option for when you do not know all the possible column headings that you may need. Pivot( max(v) for k in ( 'name', 'age', 'gender', 'status') The drawback of this solution is however, that the result can’t easily be used in a spreadsheet program.Oracle 11g provides a PIVOT operation that does what you want. All modern DBMS support JSON and generating JSON content in one way or the other, so the above solution does not only apply to Postgres. Returning the PIVOT result as a JSON value is much more flexible and can easier be changed if the requirements change.
#Pivot in oracle 10g Pc#
is_active ))) as categories from product p left join product_category pc on pc. name, jsonb_build_object ( 'active', pc. name as product, jsonb_agg ( jsonb_build_object ( c. When we return this information as JSON this is very easy: Adding that column to the “traditional” PIVOT or filtered aggregation query would massively blow up the code. The product_category table contains a flag if the assignment to the category is currently active. But much more importantly: it enables us to easily extend what we return. The value can directly be used by a HTML/JavaScript frontend. The number of distinct values in the dimension placed on x axis will not change frequently in future. The version of database server configured in the connection pool of OBIEE’s repository is Oracle 11GR1 or higher 2. JSON has some advantages: it’s a well defined format and we don’t need to think about what characters to escape. This blog will be useful to anyone who intends to improve the performance of the pivot view of OBIEE Assumptions 1.
but in forms 10g ocx controls are not supported. Why JSON? We could also easily use string_agg() to get a comma separated list of categories. Hello All, I have seen Pivot table as one of the ocx control in forms 6i.
#Pivot in oracle 10g tv#
+-ġ32cm TV Set | ġ52cm TV Set | ĥ5cm Monitor | ħ0cm Monitor | The output for the above (Postgres) query would be: product | categories Without seeing the output, it is obvious that this query doesn’t need to be changed when more categories are added. name ) as categories from product p left join product_category pc on pc. The easiest way is to return the categories as a single JSON array: on a web page, it is a lot easier to return the categories as a JSON value that can directly be used by the frontend. a spreadsheet program like LibreOffice Calc.īut if that data is intended to be displayed e.g. The above output is very usefull if the data should be imported into e.g. But just like conditional aggregation, it suffers from the same problem: the query needs to be changed each time a new category is added. Postgres also offers a crosstab function to do this. It is pretty obvious that this gets really cumbersome if a new category is added and it also doesn’t scale very well. Swiss Army Knife | | | Tools | Outdoor Equipment | | Standard Laptop | Electronics | | | | | Laptops High End Laptop | Electronics | | | | | Laptops +-+-+-+-+-+-ġ32cm TV Set | Electronics | TV Sets | | | Displays |ġ52cm TV Set | Electronics | TV Sets | | | Displays |ĥ5cm Monitor | Electronics | | | | Displays |ħ0cm Monitor | Electronics | | | | Displays | name īased on the sample data the output would look like this: product | category_1 | category_2 | category_3 | category_4 | category_5 | category_6 name = 'Laptops' ) as category_6 from product p left join product_category pc on pc. name = 'Displays' ) as category_5, max ( c. name = 'Outdoor Equipment' ) as category_4, max ( c. name = 'Tools' ) as category_3, max ( c. name = 'TV Sets' ) as category_2, max ( c. name = 'Electronics' ) as category_1, max ( c.
0 kommentar(er)
