First things first - I've done the search part! Some very usefull stuff indeed to pick up along the way, but somehow couldn't find the cure for my very simple problem, so (the code is SQL Server, but should be done in O-10g):
Create table Test (TestID int, Name varchar(50), Phone varchar(20))
Insert into Test values(1, 'John', '1234567')
Insert into Test values(2, 'Peter', '9876543')
So, I have this:
TestID Name Phone
------ ------- ----------
1 John 1234567
2 Peter 9876543
Now, what I need is a query (to become a view or stored procedure, eventually) that gives an output like this:
Row1 Row2
---------- ----------
1 2
John Peter
1234567 9876543
I can't believe how simple it looks, yet I am stuck with it for two days now (yeah, I know, the word is - embarrassing).
Nevertheless, what I have managed to acomplish so far (by using a new temp table and a cursor) looks like this:
Row1 Row2
---------- ----------
1 NULL
John NULL
1234567 NULL
NULL 2
NULL Peter
NULL 9876543
Question 1: How to query this "result table" to show it in a needed form?
Question 2: What is a best way to do the whole thing from scratch?
Question 3: Is this possible to achieve at run time, without knowing the number of rows and columns (i.e. by passing variables in a procedure call - dynamically)?
If you could shed some light on this for me, do it, please. Thank you. Cheers.
well, this is quite hard actually - and requires at least TWO steps
step 1: figure out how many columns there will be. That is, run a count(*) against the table.
step 2: dynamically construct a query that'll select out that many columns
because every query must MUST know the number of columns it has at parse time. It is required.
So, let us presume we know "2" is the number of rows. then:
ops$tkyte%ORA9IR2> select *
2 from (select testid, name, phone, row_number() over (order by testid) rn
3 from test),
4 (select level RW from dual connect by level <= 3)
5 /
TESTID Tablespace Name PHONE RN RW
---------- ------------------- -------------------- ---------- ----------
1 John 1234567 1 1
2 Peter 9876543 2 1
1 John 1234567 1 2
2 Peter 9876543 2 2
1 John 1234567 1 3
2 Peter 9876543 2 3
6 rows selected.we need to output each row in the source set as many times as we have columns - that is what the cartesian join to "(select level RW from dual connect by level <= 3)" does. We also need to assign a row number to each row in the source data set - that is what row_number() does.
Next:
ops$tkyte%ORA9IR2> select rw,
2 decode( rn, 1, decode( rw, 1, to_char(testid), 2, name, 3, phone) ) r1,
3 decode( rn, 2, decode( rw, 1, to_char(testid), 2, name, 3, phone) ) r2
4 from (select testid, name, phone, row_number() over (order by testid) rn
5 from test),
6 (select level RW from dual connect by level <= 3)
7 /
RW R1 R2
---------- ---------- ----------
1 1
1 2
2 John
2 Peter
3 1234567
3 9876543
6 rows selected.
we get something like where you are now - but we have the RW column - to group by....
and then:
ops$tkyte%ORA9IR2> select rw,
2 max(decode( rn, 1, decode( rw, 1, to_char(testid), 2, name, 3, phone) )) r1,
3 max(decode( rn, 2, decode( rw, 1, to_char(testid), 2, name, 3, phone) )) r2
4 from (select testid, name, phone, row_number() over (order by testid) rn
5 from test),
6 (select level RW from dual connect by level <= 3)
7 group by rw
8 /
RW R1 R2
---------- ---------- ----------
1 1 2
2 John Peter
3 1234567 9876543
In 11gR1, this will be simplified to:
ops$tkyte%ORA11GR1> with data
2 as
3 (
4 select rn, data, thing
5 from (select to_char(testid) testid, name, phone, to_char(rownum) rn from test )
6 unpivot ( data for thing in ( testid , name, phone) )
7 )
8 select thing, row1, row2
9 from data
10 pivot( max(data) for rn in ( '1' as row1, '2' as row2 ))
11 /
THING ROW1 ROW2
------ ---------- ----------
TESTID 1 2
PHONE 1234567 9876543
NAME John Peter
and line 10 is the part you would have to dynamically construct....