• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 478
  • Last Modified:

Using a sql query, how to select an individual row and column data?


Background:  Using an external table I loaded vertical (by column) data from an excel file.  Now I want to arrange this data in rows.  So basically I'm trying to convert data formatted in columns to rows.  From a temp table I called each column col1, col2, col3 hoping I could

select rownum, 'col'||rownum from temp_table
from temp_table
            GROUP BY rownum, 'COL'||rec_rownum HAVING rownum = 1;

but that doesn't work.

table description:

CREATE TABLE temp_table
(
  COL1   VARCHAR2(20 BYTE),
  COL2   VARCHAR2(20 BYTE),
  COL3   VARCHAR2(20 BYTE))

0
talahi
Asked:
talahi
  • 5
  • 3
1 Solution
 
sdstuberCommented:
use UNION and query each column separately
ee.txt
0
 
sdstuberCommented:
alternately, use cartesian join to unpivot your data

ee.txt
0
 
talahiAuthor Commented:
I'm not sure I see it.  

I'm trying to go from data read into a table like this (temp_table_cols)

COL1      COL2      COL3
COL1      COL2      COL3
COL1      COL2      COL3
COL1      COL2      COL3
COL1      COL2      COL3

to regular rows of data like this  (temp_table_rows),

COL1      col1      col1      col1      col1                                    
COL2      col2      col2      col2      col2                                    
COL3      col3      col3      col3      col3
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
sdstuberCommented:
your results can't be dynamic,  the number of columns must be known at the time the sql statement is parsed.

how many rows do you want to convert to columns?
0
 
talahiAuthor Commented:
Just as above, 3 columns of data converted into 3 rows of data.  
I hoped I could grab
COL1(1,1) and put it in ROW(1,1) and
COL1(1,2) into ROW(1,2) etc.
0
 
sdstuberCommented:
5 rows becomes 5 columns.

are you saying your temp_table will never have more than 5 rows of data in it?
0
 
sdstuberCommented:
so,  if you do have constant rows of   5 rows of 3 columns  that you transposed to 3 rows of 5 columns  try this...


SELECT   MAX(
             CASE
                 WHEN n = 1 AND rn = 1 THEN col1
                 WHEN n = 2 AND rn = 1 THEN col2
                 WHEN n = 3 AND rn = 1 THEN col3
             END)
             a,
         MAX(
             CASE
                 WHEN n = 1 AND rn = 2 THEN col1
                 WHEN n = 2 AND rn = 2 THEN col2
                 WHEN n = 3 AND rn = 2 THEN col3
             END)
             b,
         MAX(
             CASE
                 WHEN n = 1 AND rn = 3 THEN col1
                 WHEN n = 2 AND rn = 3 THEN col2
                 WHEN n = 3 AND rn = 3 THEN col3
             END)
             c,
         MAX(
             CASE
                 WHEN n = 1 AND rn = 4 THEN col1
                 WHEN n = 2 AND rn = 4 THEN col2
                 WHEN n = 3 AND rn = 4 THEN col3
             END)
             d,
         MAX(
             CASE
                 WHEN n = 1 AND rn = 5 THEN col1
                 WHEN n = 2 AND rn = 5 THEN col2
                 WHEN n = 3 AND rn = 5 THEN col3
             END)
             e
    FROM (SELECT ROWNUM rn, temp_table.* FROM temp_table),
         (SELECT 1 n FROM DUAL
          UNION ALL
          SELECT 2 FROM DUAL
          UNION ALL
          SELECT 3 FROM DUAL)
GROUP BY n
0
 
talahiAuthor Commented:
Wow, as usual I'm impressed, thanks again.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now