Pivoting data on the fly using Oracle 11g PIVOT clause
2 posters
Page 1 of 1
Pivoting data on the fly using Oracle 11g PIVOT clause
Hi All -
I am hoping some folks here can give sound experience advise from those having working experience with Oracle PIVOT clause introduced in 11g. Below, I am describing a scenario to see if feasible to use PIVOT for these requirements.
Requirements:
1. # of resulting columns in PIVOT are determined at query execution time; The data represents survey responses (stored as rows) which need to be pivoted to present each response in its own column. The number of responses to a particular survey will vary, the SQL SELECT statement will not use the PIVOT 'IN' clause to cherry-pick cardinal values within the column containing participant answers;
2. The # of questions and responses can be quite large! I would like to understand if there are common limitations to the # of rows that can be pivoted into columns - is the answer maximum table column size?
3. query result set will be exported to some common format - Tab-delimited, Excel, etc. I understand the limitations of end formats;
If folks are kind enough to help, I would greatly appreciate it. The goal here is to manage the columnar query results from the RDBMS, so did not want to extend the discussion to DW Appliances, columnar DB stores, BI solutions, etc.
Thanks in advance all!
I am hoping some folks here can give sound experience advise from those having working experience with Oracle PIVOT clause introduced in 11g. Below, I am describing a scenario to see if feasible to use PIVOT for these requirements.
Requirements:
1. # of resulting columns in PIVOT are determined at query execution time; The data represents survey responses (stored as rows) which need to be pivoted to present each response in its own column. The number of responses to a particular survey will vary, the SQL SELECT statement will not use the PIVOT 'IN' clause to cherry-pick cardinal values within the column containing participant answers;
2. The # of questions and responses can be quite large! I would like to understand if there are common limitations to the # of rows that can be pivoted into columns - is the answer maximum table column size?
3. query result set will be exported to some common format - Tab-delimited, Excel, etc. I understand the limitations of end formats;
If folks are kind enough to help, I would greatly appreciate it. The goal here is to manage the columnar query results from the RDBMS, so did not want to extend the discussion to DW Appliances, columnar DB stores, BI solutions, etc.
Thanks in advance all!
kminboston- Posts : 2
Join date : 2009-11-13
Re: Pivoting data on the fly using Oracle 11g PIVOT clause
1. The number of columns returned will vary as needed.
2. I do not know, offhand, what the maximum number of columns are, but it is probably at least many hundred.
2. I do not know, offhand, what the maximum number of columns are, but it is probably at least many hundred.
![-](https://2img.net/i/empty.gif)
» Model Design best practice - add columns or pivot data for multiple rows ?
» Fact Table Indexing Strategy
» Data replication of 500+ tables from Oracle to SQL Server
» Dimension Design with Multiple Data Sources (ORACLE and SQL Server)
» Multiple fact tables and a having filter clause
» Fact Table Indexing Strategy
» Data replication of 500+ tables from Oracle to SQL Server
» Dimension Design with Multiple Data Sources (ORACLE and SQL Server)
» Multiple fact tables and a having filter clause
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum
|
|