Every Row into an individual Columns in SQL Query

Hi,

As karthick pointed out, the number of columns in a result set must be hard-coded into the query.
If you want to write something today that will handle however many columns you may need tomorrow, then use Dynamic SQL, where a preliminary query writes part of your query (the part with the unknown number of columns and their names) for you.

The exam[ple below uses a SQL*Plus SPOOL file to store the variable part of the query, but it can be adapted to use a substitution variable, a PL/SQL string variable, or something else.
{code}
/*
How to Pivot a Table with a Dynamic Number of Columns

This works in any version of Oracle
The "SELECT ... PIVOT" feature introduced in Oracle 11
is much better for producing XML output.

Say you want to make a cross-tab output of
the scott.emp table.
Each row will represent a department.
There will be a separate column for each job.
Each cell will contain the number of employees in
a specific department having a specific job.
The exact same solution must work with any number
of departments and columns.
(Within reason: there's no guarantee this will work if you
want 2000 columns.)

Case 0 "Basic Pivot" shows how you might hard-code three
job types, which is exactly what you DON'T want to do.
Case 1 "Dynamic Pivot" shows how get the right results
dynamically, using SQL*Plus.
(This can be easily adapted to PL/SQL or other tools.)

NOTE: Using SQL*Plus file I/O, as in this example, is just
one of many ways to do dynamic SQL.
*/


PROMPT ========== 0. Basic Pivot ==========

SELECT deptno
, COUNT (CASE WHEN job = 'ANALYST' THEN 1 END) AS analyst_cnt
, COUNT (CASE WHEN job = 'CLERK' THEN 1 END) AS clerk_cnt
, COUNT (CASE WHEN job = 'MANAGER' THEN 1 END) AS manager_cnt
FROM scott.emp
WHERE job IN ('ANALYST', 'CLERK', 'MANAGER')
GROUP BY deptno
ORDER BY deptno
;


PROMPT ========== 1. Dynamic Pivot ==========

-- ***** Start of dynamic_pivot.sql *****

-- Suppress SQL*Plus features that interfere with raw output
SET FEEDBACK OFF
SET PAGESIZE 0

SPOOL p:\sql\cookbook\dynamic_pivot_subscript.sql

SELECT DISTINCT
', COUNT (CASE WHEN job = '''
|| job
|| ''' ' AS txt1
, 'THEN 1 END) AS '
|| job
|| '_CNT' AS txt2
FROM scott.emp
ORDER BY txt1;

SPOOL OFF

-- Restore SQL*Plus features suppressed earlier
SET FEEDBACK ON
SET PAGESIZE 50

SPOOL p:\sql\cookbook\dynamic_pivot.lst

SELECT deptno
@dynamic_pivot_subscript
FROM scott.emp
GROUP BY deptno
ORDER BY deptno
;

SPOOL OFF

-- ***** End of dynamic_pivot.sql *****

/*
EXPLANATION:
The basic pivot assumes you know the number of distinct jobs,
and the name of each one. If you do, then writing a pivot query
is simply a matter of writing the correct number of ", COUNT ... AS ..."\
lines, with the name entered in two places on each one. That is easily
done by a preliminary query, which uses SPOOL to write a sub-script
(called dynamic_pivot_subscript.sql in this example).

The main script invokes this sub-script at the proper point.
In practice, .SQL scripts usually contain one or more complete
statements, but there's nothing that says they have to.
This one contains just a fragment from the middle of a SELECT statement.

Before creating the sub-script, turn off SQL*Plus features that are
designed to help humans read the output (such as headings and
feedback messages like "7 rows selected.", since we do not want these
to appear in the sub-script.
Turn these features on again before running the main query.

*/