CODEWITHSUNDEEP
sqldatabasevieworacle11gmultiple-columns
Psychocryo
Psychocryo

Reputation: 2153

how to create a view of multiple columns from one column structure in oracle sql?

suppose that i have a table named PROFILE and one column of it is names like below:

|NAMES|
-------
JAMES
-------
ANDY
-------
PETER
-------
SARAH
-------
JESSICA
-------
RACHEL

How could i make a view so that it shows like this:

|col1  | col2| col3   |
-------|-----|--------|
|JAMES  |PETER|JESSICA|
-------|-----|------- |
|ANDY   |SARAH|RACHEL |
----------------------

The reason i'm in need of this is because i need to use it with apex report page,i'm planning to do a grid style layout.Kinda hard to explain but thats the structure i would like to achieve. Would it be possible? and would the query be too many lines?

Upvotes: 1

Views: 1967

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270011

I think the easiest way to approach this is to use Oracle's built-in rownum variable and then use integer division (to determine the row) and mod() to determine the column:

SELECT MAX(CASE WHEN mod(seqnum, 3) = 0 THEN name END) as col1,
       MAX(CASE WHEN mod(seqnum, 3) = 1 THEN name END) as col2,
       MAX(CASE WHEN mod(seqnum, 3) = 2 THEN name END) as col3
FROM (SELECT name, rownum as seqnum
      FROM profile
     ) t
GROUP BY trunc((seqnum - 1) / 3)

Upvotes: 1

sgeddes
sgeddes

Reputation: 62831

Assuming you always need 3 columns, then this will work using MAX with CASE to perform a pivot, along with ROW_NUMBER() and MOD():

SELECT MAX(CASE WHEN rn = 1 THEN name END) col1,
  MAX(CASE WHEN rn = 2 THEN name END) col2,
  MAX(CASE WHEN rn = 3 THEN name END) col3
FROM (
  SELECT Row_Number() Over (Partition By MOD(ROWNUM,2) Order By Null) rn, MOD(ROWNUM,2) whichGroup, name
  FROM profile
  ) talias
GROUP BY whichGroup

Use MOD to get which rows and use Row_Number for your pivot.

SQL Fiddle: http://sqlfiddle.com/#!4/46149/1

-- Please note, without adding a field to your ORDER BY clause, you won't be able to guarantee a specific order to the results.

Upvotes: 3

Related Questions