This content has been marked as final. Show 7 replies
query for 10g would be
from (select job,
group by job)
order by 1;
Pivot is used to convert Rows to columns.
I think u need to use union as
select col1 from table
select col2 from table
select col252 from table
Union possibly can be done for lesser number of columns.
but, i have 252 columns where in approx I need nearly 60 columns to be converted to rows.
this is very huge to write using union.
can you pls mention any other option instead of this a we would have too many lines of code in a single select statement.
checked links given by you. links shows examples with 2 or 3 columns, but I am referring to nearly 60 columns.
Will that not be performace problem if i use same method in my issue.
pls suggest, if possible, any other method where in i can work out for more columns.
Will that not be performace problem if i use same method in my issue.It depends on how many records you're querying and how often.
You want to turn 60 columns into 60 rows?
Should not be a problem.
You could load those records into a new table and let your report query on that new table.
Always post your Oracle version. Don't just add an easy-to-miss tag like "10G" to your message; say something clear, like "I'm using Oracle 10.2.0.4.0".
This is called Unpivot . One way to do it is to cross-join your table to a Counter Table , any table (or sub-query, as in the example below) that has as many rows as you need in your output.
To unpivot all 252 columns:
The nested CASE expressions are necessary because you can't have more than 127 WHEN-THEN pairs in a single CASE expression.
WITH cntr AS ( SELECT LEVEL AS n FROM dual CONNECT BY LEVEL <= 252 ) SELECT c.n , CASE WHEN c.n <= 100 THEN CASE c.n WHEN 1 THEN x.col_1 WHEN 2 THEN x.col_2 ... WHEN 100 THEN x.col_100 END WHEN c.n <= 200 THEN CASE c.n WHEN 101 THEN x.col_101 WHEN 102 THEN x.col_102 ... WHEN 200 THEN x.col_200 END WHEN c.n <= 300 THEN CASE c.n WHEN 201 THEN x.col_201 WHEN 202 THEN x.col_202 ... WHEN 252 THEN x.col_252 END END AS data_col FROM cntr c CROSS JOIN table_x x ;
If you're choosing 60 columns out of the 252 to unoivot, then you only need one CASE expression.