Pivoting data on the fly using Oracle 11g PIVOT clause

View previous topic View next topic Go down

Pivoting data on the fly using Oracle 11g PIVOT clause

Post  kminboston on Wed Mar 03, 2010 12:41 pm

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!

kminboston

Posts : 2
Join date : 2009-11-13

View user profile

Back to top Go down

Re: Pivoting data on the fly using Oracle 11g PIVOT clause

Post  ngalemmo on Wed Mar 03, 2010 3:05 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum