Of course, having to specify the values in the in clause defeats the goal of having a completely dynamic pivot/crosstab query as was the desire of this question's poster. 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') , then pivot it using GROUP BY column_name.Oracle 11g provides a PIVOT operation that does what you want. I was wondering how I could use pivoting to help me get rid of duplicate ID rows and just add an extra column for their other emails. When you need to display it with columns q1, q2. , then pivot it, using GROUP BY type_col. pivotinclause defines a filter for column(s) in the pivotforclause. pivotforclause specifies the column that you want to group or pivot. When you need to display it with columns x_amt, y_amt. The pivotclause performs an implicitly GROUP BY based on all columns which are not specified in the clause, along with values provided by the pivotinclause. Perhaps the table should be permanently stored in a form pretty much like unpivoted_data, above. These 2 sub-scripts are written by 2 prelimiary queries, right before the main query.Īs others have said, the fact that you're asking this question hints at a poor table design. As you can see, the main query looks exactly like the static query, except that the two dynamic sections have been replaced by sub-scripts. After writing sub-scripts, turn on features designed for human readers SELECT DISTINCT ', SUM (CASE WHEN type_val = ''' Before writing sub-scripts, turn off features designed for human readersĪND column_name NOT IN ('EMP_ID', 'TYPE_VAL') Instead of hard-coding those 2 dynamic sections, have Preliminary Queries write them for you, a split second before you run the main query, by running this script: You want to derive all that from the data actually in test_emp. (2) in the main query, we had to know that there would be 4 pivoted columns in the rsult set, and that they would be called q1, q2, q3 and q4. You want to derive all of that from all_tab_columns. 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.
(1) in the sub-query unpivoted_data, we had to know that there were 3 columns to be unpivoted, and that they were called x_amt, y_amt and z_amt. This blog will be useful to anyone who intends to improve the performance of the pivot view of OBIEE Assumptions 1. Column names are hard-coded in two places: , SUM (CASE WHEN type_val = 'Q4' THEN v ELSE 0 END)ĚS q4 - ***** Dynamic section 2 ***** How can I create a query that will provide this pivot view in Oracle 10g UserName Chair Table Bed - John Doe 4 1 0 Jane Doe 0 2 1. , SUM (CASE WHEN type_val = 'Q3' THEN v ELSE 0 END)ĚS q3 - ***** Dynamic section 2 ***** , SUM (CASE WHEN type_val = 'Q2' THEN v ELSE 0 END)ĚS q2 - ***** Dynamic section 2 ***** , SUM (CASE WHEN type_val = 'Q1' THEN v ELSE 0 END)ĚS q1 - ***** Dynamic section 2 ***** WHEN 'Z_AMT' THEN z_amt - ***** Dynamic section 1 ***** WHEN 'Y_AMT' THEN y_amt - ***** Dynamic section 1 ***** WHEN 'X_AMT' THEN x_amt - ***** Dynamic section 1 ***** So one possibility is to do this in two phases, first fetch the ranges and. This cannot be achieved if the list of columns would by dynamic. When a SQL statement is executed, the system needs to know the structure of the result set at compile time. Here's a dynamic way to do this is Oracle 10, using theSQL*Plus command to handle the dynamic parts.įirst, let's see how we would do this using a static query:ĪND column_name NOT IN ('EMP_ID', 'TYPE_VAL') As far as I know you cannot achieve dynamic pivoting.
0 kommentar(er)
