how to get sql select query result as column vs value wise instead of row wise without using PIVOT

Question

Lets say I have a table called student:

When I select from that table the result is:

SQL> select * from student;
NAME                 ROLL_NO
-------------------- --------------------
gokul                3
gokul1               34

Instead can we get the output like this? without using PIVOT keyword

SQL> select * from student;

NAME     gokul       gokul1     
ROLL_NO  3           34

Can you please better format your code? Is it MySQL or Oracle? — Aleksej, Apr 07 '16 at 08:32
What you want is called pivot table or cross tabulated (crosstab) query. SO has plenty of answers for this question both for mysql and oracle. — Shadow, Apr 07 '16 at 08:39
Possible duplicate of [Oracle SQL PIVOT Table](http://stackoverflow.com/questions/20551262/oracle-sql-pivot-table) — Shadow, Apr 07 '16 at 08:47
@Shadow That question has a fixed number of output columns so it is possible to do a pivot purely in SQL. Unless this question is limiting itself to a fixed set of students then it is about a dynamic pivot and is entirely different. — MT0, Apr 07 '16 at 09:00
I'm not a big expert in oracle, but a table surely has fixed number of columns, not dynamic number. Pls correct me if I'm wrong there. Again, the roll_no could have a limited range. You made an assumption that the OP is about dynamic number of columns. I did not. — Shadow, Apr 07 '16 at 09:04
@Shadow In this case, the input table has a fixed number of columns but the output has a dynamic number of columns as there are a dynamic number of students. In the question you have linked there were a fixed number of columns in the input table and a fixed number of output columns. — MT0, Apr 07 '16 at 09:06
@MT0 Again, you made an assumption, I made another one. But even if the question is about dynamic pivoting, that has answers in SO already. See for example: http://stackoverflow.com/questions/15491661/dynamic-pivot-in-oracle-sql — Shadow, Apr 07 '16 at 09:10

MT0 · Accepted Answer · 2016-04-07T10:25:54.717

The simple answer is: no, you cannot.

In Oracle, it is possible to do a dynamic pivot on an unknown number of columns but it is not something that you can do using a simple SELECT * FROM student. You will need to use PL/SQL to build some dynamic SQL:

VARIABLE cur REFCURSOR;

DECLARE
  p_sql CLOB;
BEGIN
  SELECT 'SELECT ''ROLL_NO'' AS "NAME", '
         || LISTAGG(
              'MAX( CASE name WHEN '''
                || name
                || ''' THEN roll_no END ) AS "'
                || name || '"',
              ','
            ) WITHIN GROUP ( ORDER BY ROWNUM )
         || ' FROM students'
  INTO   p_sql
  FROM   students;

  OPEN :cur FOR p_sql;
END;
/

PRINT cur;

Output:

NAME    gokul       gokul1     
------- ----------- -----------
ROLL_NO 3           34

Edit

The above code will work where the LISTAGG output is under 4000 bytes but if you want to go over that then you need a slightly different method:

VARIABLE cur REFCURSOR;

DECLARE
  TYPE name_t IS TABLE OF students.name%type;
  p_sql CLOB;
  names name_t;
BEGIN
  SELECT DISTINCT name
  BULK COLLECT INTO names
  FROM   students;

  p_sql := EMPTY_CLOB() || 'SELECT ''ROLL_NO'' AS "NAME"';
  FOR i IN 1 .. names.COUNT LOOP
    p_sql := p_sql || ', MAX( CASE name WHEN '''
                   || names(i)
                   || ''' THEN roll_no END ) AS "'
                   || names(i)
                   || '"';
  END LOOP;
  p_sql := p_sql || ' FROM students';

  OPEN :cur FOR p_sql;
END;
/

PRINT cur;

how to get sql select query result as column vs value wise instead of row wise without using PIVOT

1 Answers1