2 Replies Latest reply: Jun 21, 2013 6:28 AM by Hoek RSS

    select N columns and N rows from a table

    874273

      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