2 Replies Latest reply on Jun 21, 2013 11:28 AM by Hoek

# select N columns and N rows from a table

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

• ###### 1. Re: select N columns and N rows from a table

Hi Blu,

Regards

SA

• ###### 2. Re: select N columns and N rows from a table

We could be of much better help if you could:

- post the actual and complete error code and message you're getting