Let's assume we have a fairly simple table called PIVOTTEST this table, apart from displaying a distinct lack of imagination as far as naming goes, contains four columns ID (number), NAME (varchar2), DATE_CREATED (date) and DATE_LAST_UPDATED (date). It's created using the SQL:
create table PIVOTTEST
(
ID number,
NAME varchar2(60),
DATE_CREATED date,
DATE_LAST_UPDATED date
);
In order to do our test let's put a few records into the table;
insert into pivottest(id, name, date_created, date_last_updated) values (1, 'ANDY', sysdate-200, sysdate - 5);
insert into pivottest(id, name, date_created, date_last_updated) values (2, 'BRETT', sysdate-190, sysdate - 4);
insert into pivottest(id, name, date_created, date_last_updated) values (3, 'COLIN', sysdate-180, sysdate - 3);
insert into pivottest(id, name, date_created, date_last_updated) values (4, 'IAN', sysdate-170, sysdate - 2);
insert into pivottest(id, name, date_created, date_last_updated) values (5, 'ADAM', sysdate-160, sysdate - 1);
commit;
If we do a SELECT * FROM PIVOTTEST WHERE ID = 1 we get a single record back:
ID NAME DATE_CREATED DATE_LAST_UPDATED
1 ANDY 10-MAY-2008 21-NOV-2008
Yours dates will be different, and the format will be determined by your system settings but you get the point.
Assuming we'd prefer to have the results as multiple columns we would be aiming at something looking like:
Column Name Column Value
ID 1
NAME ANDY
DATE_CREATED 10-MAY-2008
DATE_LAST_UPDATED 21-NOV-2008
The easiest way to do this is to use multiple UNIONS and select each field we're interested in in turn:
select 1 ID, 'ID' Name, to_char(ID) Value from APPS.PIVOTTEST where ID = 1
union
select 2 ID, 'NAME' Name, NAME Value from APPS.PIVOTTEST where ID = 1
union
select 3 ID, 'DATE_CREATED' Name,to_char(DATE_CREATED) Value from APPS.PIVOTTEST where ID = 1
union
select 4 ID, 'DATE_LAST_UPDATED' Name, to_char(DATE_LAST_UPDATED) Value from APPS.PIVOTTEST where ID = 1
First thing; in order to allow the UNION to work the columns have to be of the same type. I've go for "character" just because it's the one practically every type has in common. In theory it will depend on the data you're working with but in practice you'll almost certainly want to use characters!
You'll notice that I've included the "WHERE ID =1" clause at the end to just give me the record I'm interested in and I've numbered the select statements so that when they are all joined together with the UNION the columns still come out in the order I'm expecting (if you remove the 1, 2, 3, 4 from the SELECT ... ID then the records come back in alphabetical column name order ... do you want that?).
Because the table details are held in Oracle you can actually do the same thing using a script:
-- Created on 25-Nov-2008 by APE06
declare
-- Local variables here
cursor c_Columns is
select atc.COLUMN_ID,
atc.owner,
atc.table_name,
atc.COLUMN_NAME,
atc.DATA_TYPE
from all_tab_columns atc
where atc.owner = 'APPS'
and atc.TABLE_NAME = upper('PIVOTTEST')
order by atc.COLUMN_ID;
v_Where varchar2(255) := 'ID = 1';
begin
for v_Column in c_Columns loop
dbms_output.put_line('select ' || v_Column.column_id ||
' ID, ''' || v_Column.column_name ||
''' Name, nvl(' ||
case
when v_Column.Data_Type = 'DATE' then 'to_char(' || v_Column.column_name || ',''DD-MON-YYYY'')'
when v_Column.Data_Type = 'NUMBER' then 'to_char(' || v_Column.column_name || ')'
else v_Column.column_name
end ||
', ''
v_Column.Table_name || ' where ' || v_Where);
dbms_output.put_line('union');
end loop;
end;
You need to change the OWNER (from APPS), the TABLE_NAME (from PIVOTTEST), and your where clause condition to return a single row (from ID = 1) and then you're ready to go.
You should also watch out for the "union" that gets tacked on the end ... you'll need to delete that (I could have added a "select '','' from dual where 3=1" to get rid of it but ... well you can do that yourselves now can't you? (I'm also using PL/SQL Developer a a test window which makes copy/pasting very easy so I don't really need 100% accuracy).
This script will generate the SQL to query the table as a Column Name/ Value combination - it also does a few other "nice" things like specifying the format for the date and displaying
I hope this helps!
