-2

Please share some SQL ans for the below req:

I want to return all of the rows in a single-column table as a single row, with the value from each row pivoted into a column in the output.

SAMPLE_COLUMN_DATA
------------------
    SAM ANDREW
    JOHN MATHEW
    CHRIS JOE
    JANE PATRICK

OUTPUT NEEDED AS BELOW:

col1       |col2        |col3      |col4
------------------------------------------------
SAM ANDREW |JOHN MATHEW |CHRIS JOE |JANE PATRICK
jscott
  • 1,011
  • 8
  • 21
Indu
  • 5
  • 3

2 Answers2

1

The way I understood the question, it is a matter of aggregation (listagg, with pipe character as a separator):

SQL> with test (sample_column_data) as
  2    (select 'SAM ANDREW'   from dual union all
  3     select 'JOHN MATHEW'  from dual union all
  4     select 'CHRIS JOE'    from dual union all
  5     select 'JANE PATRICK' from dual
  6    )
  7  select listagg(sample_column_data, '|') within group (order by null) result
  8  from test;

RESULT
--------------------------------------------------------------------------------
CHRIS JOE|JANE PATRICK|JOHN MATHEW|SAM ANDREW

SQL>

If they have to be "true" separate columns, then you could use ROWNUM with aggregation along with CASE. This, of course, doesn't scale at all.

SQL> with test (sample_column_data) as
  2    (select 'SAM ANDREW'   from dual union all
  3     select 'JOHN MATHEW'  from dual union all
  4     select 'CHRIS JOE'    from dual union all
  5     select 'JANE PATRICK' from dual
  6    )
  7  select
  8    max(case when rownum = 1 then sample_column_data end) col1,
  9    max(case when rownum = 2 then sample_column_data end) col2,
 10    max(case when rownum = 3 then sample_column_data end) col3,
 11    max(case when rownum = 4 then sample_column_data end) col4
 12  from test;

COL1         COL2         COL3       COL4
------------ ------------ ---------- ------------
SAM ANDREW   JOHN MATHEW  CHRIS JOE  JANE PATRICK

SQL>
Littlefoot
  • 131,892
  • 15
  • 35
  • 57
  • i have used this pipeline symbol to differencite between columns.. Thats is not result what i am expecting.. what i need is , if you select a table, how you see the columns from that table ... in the same way i would like to see these values as separate columns. please let me know if you need more detail. – Indu Aug 27 '21 at 12:50
  • I edited the answer and added another example; have a look. – Littlefoot Aug 27 '21 at 12:55
0

In your question you have one column with values (names)..

In that case, you can use listagg() function:

select 
        listagg(SAMPLE_COLUMN_DATA,'|') within group 
         (order by SAMPLE_COLUMN_DATA) 
from dual;
Georgy
  • 428
  • 2
  • 16