This discussion is archived
2 Replies Latest reply: Jun 21, 2013 4:28 AM by Hoek RSS

select N columns and N rows from a table

874273 Newbie
Currently Being Moderated

Hi Blushadow,

 

Last week I saw your post regarding N columns and N rows from a table. For this You have given nice example. But I tried with a table(EMP). But I am getting error.

Can you please explaine me on EMP table.

 

{code}

 

1  with t as (select &c as c, &r as r from dual)
      2  select max(col1) as col1
      3        ,max(col2) as col2
      4        ,max(col3) as col3
      5        ,max(col4) as col4
      6        ,max(col5) as col5
      7        ,max(col6) as col6
      8        ,max(col7) as col7
      9        ,max(col8) as col8
    10        ,max(col9) as col9
    11        ,max(col10) as col10
    12  from (
    13        select floor((level-1)/c) as lvl
    14              ,case when mod(level-1,c) = 0 then level else null end as col1
    15              ,case when mod(level-1,c) = 1 then level else null end as col2
    16              ,case when mod(level-1,c) = 2 then level else null end as col3
    17              ,case when mod(level-1,c) = 3 then level else null end as col4
    18              ,case when mod(level-1,c) = 4 then level else null end as col5
    19              ,case when mod(level-1,c) = 5 then level else null end as col6
    20              ,case when mod(level-1,c) = 6 then level else null end as col7
    21              ,case when mod(level-1,c) = 7 then level else null end as col8
    22              ,case when mod(level-1,c) = 8 then level else null end as col9
    23              ,case when mod(level-1,c) = 9 then level else null end as col10
    24        from t
    25        connect by level <= (r*c)
    26       ) x
    27  group by x.lvl
    28* order by x.lvl
    SQL> /
    Enter value for c: 4
    Enter value for r: 7
    old   1: with t as (select &c as c, &r as r from dual)
    new   1: with t as (select 4 as c, 7 as r from dual)

          COL1       COL2       COL3       COL4       COL5       COL6       COL7       COL8       COL9   COL10
    ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
             1          2          3          4
             5          6          7          8
             9         10         11         12
            13         14         15         16
            17         18         19         20
            21         22         23         24
            25         26         27         28

    7 rows selected.

    SQL> /
    Enter value for c: 10
    Enter value for r: 5
    old   1: with t as (select &c as c, &r as r from dual)
    new   1: with t as (select 10 as c, 5 as r from dual)

          COL1       COL2       COL3       COL4       COL5       COL6       COL7       COL8       COL9   COL10
    ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
             1          2          3          4          5          6          7          8          9         10
            11         12         13         14         15         16         17         18         19         20
            21         22         23         24         25         26         27         28         29         30
            31         32         33         34         35         36         37         38         39         40
            41         42         43         44         45         46         47         48         49         50

{code}

 

It is very nice of you if you can explain with EMP table...!!

 

Regards

SA

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points