Hi Tom,

great "trick". What about 9i ? Same trick or is there some better one ?

Thanks in advance.
Georg

Hi Tom

I have to create a pivot view for a table with more than 200 attributes in the view and 250 million rows per month. The group by operation cost many resources in temp tablespace (size 50 GB) and huge of cpu time for sort and aggregate.

Do you know a trick for build a pivot WITHOUT group by ? Is there a way to do it with analytic functions ? Many thanks for your feedback.

Kind Regards
Marc

If you search your site for 'row to columns' this thread doesn't come up at all, I went up to about the 50th.

Thanks so much for all of your time and effort, I and countless others really do appreciate it

good.
I have information like below in tables T1 and T2.

SQL> desc t1;
 Name                            Null?    Type
 ------------------------------- -------- ----
 A                                        NUMBER
 B                                        NUMBER
 C                                        NUMBER
SQL> select * from t1;
        A         B         C
--------- --------- ---------
       23        45        60
       33        55        70
       33        23        70
SQL> desc t2
 Name                            Null?    Type
 ------------------------------- -------- ----
 D                                        VARCHAR2(10)
 I                                        NUMBER
SQL> select * from t2;
D                  I
---------- ---------
TEST              23
TEST1             45
TEST2             60
TEST3             33
TEST4             55
TEST5             70

I need output as folows:
        A D               B D                  C D
--------- ------------------ ---------- --------- ----------
       23 TEST            45 TEST1             60 TEST2
       33 TEST3           23 TEST              70 TEST5
       33 TEST3           55 TEST4             70 TEST5

Nothing is fixed.

Thanks in advance.
 

Hi,

select q.a, q.d, p.b, p.d, s.c, s.d from
(select distinct x.a a, y.d d from t1 x, t2 y
where y.i = x.a) q,
(select distinct x.b b, y.d d from t1 x, t2 y
where y.i = x.b) p,
(select distinct x.c c, y.d d from t1 x, t2 y
where y.i = x.c) s, t1
where q.a = t1.a and p.b = t1.b and s.c = t1.c
/

I used above query to get the result.
Can u explain me what is the diff???

Thank you.

I hope inline views are faster in quires any advise?

" group by does not have to sort 
necessarily.
"

SQL> select deptno, sum(sal)
  2  from emp1
  3  group by deptno;

    DEPTNO   SUM(SAL)
---------- ----------
        10       8750
        20      10875
        30       9400

when does a group by sorts automatically , when does it not...

Please illsutrate. 

Tom,
can this be done in one single sql statement without looping.

set serveroutput on size 20000
declare
procedure temp is
v_str varchar2(76):= '';
begin
for v_rec in (select 'a' col from t1 where rownum < 77)
loop
v_str := v_str || v_rec.col;
end loop;
dbms_output.put_line(v_str) ;
end;

begin
temp;
end;
/

aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa

that rownum < 77 is not fixed it can vary.

Thanks,
M S Reddy

Tom ,
That number 77 is not fixed .The column does not always have a value 'a' it can be anything else.
consider this example.

T1
-------------------------------
c1 c2 c3
-------------------------------
a b c
d e f
g h i
j k l

the result should be

-------------------------------
c1 c2 c3
-------------------------------
adgj behk cfil

Can this be done in single sql statement?

Thanks,
M S Reddy.

Hi Tom,
I have two problems in this query:
select item_code,inventory_id,
sum(decode(monthly,'Jan',cnt,null)) jan,
sum(decode(monthly,'Feb',cnt,null)) feb,
sum(decode(monthly,'mart',cnt,null)) mart
from(
select i.item_code, i.inventory_id,
decode(to_char(b.ship_date,'mon/yy'),'jan/03','Jan'
,'feb/03','Feb'
,'mar/03','mart') as monthly,
sum(NVL(a.itm_shipped,0)) as cnt
from inventory i,order_items a, orders b
where i.inventory_id=a.inventory_id(+)
and i.account_id=b.account_id
and a.order_id=b.order_id
and i.account_id=20
and ship_date>='01-Jan-03'
and ship_date <='18-Dec-03'
and rownum < 500
group by i.item_code ,i.inventory_id ,
decode(to_char(b.ship_date,'mon/yy'),'jan/03','Jan'
,'feb/03','Feb'
,'mar/03','mart')
)
group by item_code, inventory_id;
1. It's tooooo slowly.
2. I do not now months user can pick up, for all year, a few months, or just sysdate. I need something dynamic.
I use this query in Coldfusion.
Please help.

I made it different way but it takes a lot of time.
first I pick up all id from inventory table, than in loop
I pass id to another query and find sum and month.
select id,col1,col2 ... from i
loop
select sum(item),to_char(b.ship_date,'mon/yy'),
from a,b
where a.id=query1.id
from date1 to date2
group by to_char(b.ship_date,'mon/yy')
end loop
So if second query doesn't return any record I show first select. Let's say, if first query returns 1700 rows
I have to show in report 1700 rows with blank in sum field.
Problem only in time. Too much calls and user defently will lose patient.
Can I do it in one select and faster?

Hi

I have following query

select executions, hash_value
from STATS$SQL_SUMMARY
where hash_value in (3822005349, 3255899958, 635556189)
and snap_id between 200 and 222

I would like to know if there is a way in SQL to make this query run against several snap_id. Right now I have to repeat the query for each

and snap_id between 200 and 222

is it possible?

sorry I was not clear

right now I do this

select executions, hash_value
from STATS$SQL_SUMMARY
where hash_value in (3822005349, 3255899958, 635556189)
and snap_id between 201 and 202

select executions, hash_value
from STATS$SQL_SUMMARY
where hash_value in (3822005349, 3255899958, 635556189)
and snap_id between 202 and 203

select executions, hash_value
from STATS$SQL_SUMMARY
where hash_value in (3822005349, 3255899958, 635556189)
and snap_id between 203 and 204

and so on until

select executions, hash_value
from STATS$SQL_SUMMARY
where hash_value in (3822005349, 3255899958, 635556189)
and snap_id between 221 and 222

I have to run many times the same query with different snap_id range, I was wondering if I can run all these SQLs in a go in SQL or I need to use PL/SQL?

Hi

Sorry for the delay..

I tried this query

select hash_value, snap_id, lead(snap_id) over (order by snap_ID) leadsnap, cpu_time
from STATS$SQL_SUMMARY
where snap_id between 383 and 385
and hash_value = 4133308760

HASH_VALUE SNAP_ID LEADSNAP CPU_TIME
---------- ---------- ---------- ----------
4133308760 383 384 887070000
4133308760 384 385 887210000
4133308760 385 887310000


What I want is this

select min(snap_id) || ', ' || max(snap_id) snap_range, (max(cpu_time) - min(cpu_time))/1000000 cpu_used
from STATS$SQL_SUMMARY
where hash_value = 4133308760
and snap_id between 383 and 384
union all
select min(snap_id) || ', ' || max(snap_id) snap_range, (max(cpu_time) - min(cpu_time))/1000000 cpu_used
from STATS$SQL_SUMMARY
where hash_value = 4133308760
and snap_id between 384 and 385

SNAP_RANGE CPU_USED
-------------- ----------
383, 384 .14
384, 385 .1


possible :-?

ops$tkyte@ORA10G> select *
  2    from (
  3  select hash_value,
  4         snap_id  || ',' ||
  5         lead(snap_id) over (order by snap_ID) snapid,
  6         (lead(cpu_time) over (order by snap_ID) - cpu_time)/1000000 cpu
  7  from t
  8  where snap_id between 383 and 385
  9    and hash_value = 4133308760
 10        )
 11   where snapid not like '%,'
 12  /
 
HASH_VALUE SNAPID            CPU
---------- ---------- ----------
4133308760 383,384           .14
4133308760 384,385            .1
 
 

Hi again

I rewrote the query not it looks like this

select snap_id || ', ' || leadsnap snap_range, (cpu_time - cpu_used)/1000000 cpu_time
from
(select hash_value, snap_id, lead(snap_id) over (order by snap_ID) leadsnap, cpu_time, lead(cpu_time) over (order by snap_id) cpu_used
from STATS$SQL_SUMMARY
where snap_id between 383 and 387
and hash_value = 4133308760)

SNAP_RANGE CPU_TIME
-------------- ----------
383, 384 -.14
384, 385 -.1
385, 386 -.55
386, 387 -.51
387,


It works, how do you get rid of last row though?

Hi

I am writing a procedure to analyza tables, basically I have a table with these two columns

table_name
column_name

data looks like this

table_name column_name
----------- -------------
emp deptno
emp mgr

What I am trying to do is get this output

deptno, mgr

This is because I need to analyze histograms for certain columns for certain tables.... Of course the number of rows can vary depending on number of columns required to gather histograms

Cheers

We have a person table and a telephone table
Person
Person ID

Telephone
Person ID -- Can be NULL
tel_Num
default flag

Result set we looking for
Person id , tel# (default) , tel#2
1 1231234 NULL
2 NULL NULL

Problem i am facing is that i want only two numbers even if there are more than
two in the telephone table for one person.

How can this be done

Hi tom

I was wondering how i can get the results of this query to pivot.

My querie

SELECT emp.ID ID1, hr.id ID2, emp.NAME name1, hr.NAME name2, emp.dept dept1, hr.dept dept2
FROM employees emp, human_resources hr
WHERE emp.ID = hr.ID;

Returns the following results:

ID1 ID2 Name1 Name2 Dept1 Dept2
-------------------------------------------
154 154 Jones Smith HR HR

The desired output i need is for the results to be pivoted so they appear as follows:

x y
------------
154 154
Jones Smith
HR HR

Is this possible?

I would be very grateful of any help you can give me.

Thanks in advance

hi tom ,

I require a help in pivot query.
Without using xml functions in the query i get the correct expected result. But when i add the two extract in the query i get multiple records.


When i execute this query
SELECT
ord.id OrderID
,max(decode(ownertypeid, 2, FileName, NULL)) AblaufData
,max(decode(ownertypeid, 3, FileName, NULL)) TypeData
,max(decode(ownertypeid, 4, FileName, NULL)) RegistryKOnfig
-- ,EXTRACT(fc.SequenceData, '/MEPSequenceData/@LastTimeStamp/text()').GetStringVal() seq_LastTimeStamp
-- ,EXTRACT(fc.TypData, '/Data/@LastTimeStamp/text()').GetStringVal() typ_LastTimeStamp
FROM
tabl_order ord
,tabl_fileinfo fi
,tabl_filecontent fc
,tabl_measurementlog mlog
WHERE
mlog.orderid = ord.id
AND fi.id = fc.fileid
AND ((mlog.templateid = fi.ownerid AND mlog.templateversion = fi.additionalkey)
OR (ord.id = fi.ownerid))
GROUP BY
ord.id
-- , EXTRACT(fc.SequenceData, '/MEPSequenceData/@LastTimeStamp/text()').GetStringVal()
-- , EXTRACT(fc.TypData, '/Data/@LastTimeStamp/text()').GetStringVal()
/

i get correct results. Single Record.

But i also want an attribute from the xml file. So i use xml functions to get the data.
And at the end i want single record from the query.

But when i uncomment the EXTRACT part from the column clause of the sql statement, i get two records.

Basically i get all the required data in a single view basd on the above query and generate xml out of it.



How can this be fixed.

Sorry for the delayed posting. Please refer my earlier thread above with the same Title.
Here is a test case and data for your reference.

create table tabl_order
( ID NUMBER
,ORDERNAME VARCHAR2(32))
/

create table tabl_measurementlog
( ID NUMBER
,ORDERID NUMBER
,TEMPLATEID NUMBER
,TEMPLATEVERSION NUMBER(8,2))
/

create table tabl_fileinfo
( ID NUMBER
,FILENAME VARCHAR2(32)
,OWNERID NUMBER
,ADDITIONALKEY NUMBER(8,2)
,OWNERTYPEID NUMBER)
/

create table tabl_filecontent
( FILEID NUMBER
,SEQUENCEDATA SYS.XMLTYPE
,TYPDATA SYS.XMLTYPE)
/

insert into tabl_order values(259206, 'MeasurementOrder')
/

insert into tabl_measurementlog values(1, 259206, 259023, 1.1)
/

insert into tabl_fileinfo values(1, 'Typdaten.xml',259206, null, 3)
/

insert into tabl_fileinfo values(2, 'Ablaufdaten.xml',259023, 1.1, 2)
/

insert into tabl_filecontent values
(1
,null
,xmltype('<?xml version="1.0" encoding="UTF-8"?>
<Data xmlns:xsi="</code> http://www.w3.org/2001/XMLSchema-instance"

LastTimeStamp="20040430171532">
    <Allgemein>
        <Allgemein.Row TimeStamp="20040430171532">
            <Allgemein.Column id="Name" DataType="text">XMLAblDatenWithSchema</Allgemein.Column>
            <Allgemein.Column id="Version" DataType="text">0.1</Allgemein.Column>
        </Allgemein.Row>
    </Allgemein>
</Data>'))
/

insert into tabl_filecontent values
(2
,xmltype('<?xml version="1.0" encoding="UTF-8"?>
<MEPSequenceData xmlns:xsi="

http://www.w3.org/2001/XMLSchema-instance" <code>LastTimeStamp="20030330181532">
<Allgemein>
<Allgemein.Row TimeStamp="20040430171532">
<Allgemein.Column id="Name" DataType="text">XMLAblDatenWithSchema</Allgemein.Column>
<Allgemein.Column id="Version" DataType="text">0.1</Allgemein.Column>
</Allgemein.Row>
</Allgemein>
</MEPSequenceData>')
, null)
/

set pagesize 100
set linesize 192
column orderid format 9999999
column AblaufData format a20
column TypeData format a20
column typ_LastTimeStamp format a20
column seq_LastTimeStamp format a20

SELECT
ord.id OrderID
,max(decode(ownertypeid, 2, FileName, NULL)) AblaufData
,max(decode(ownertypeid, 3, FileName, NULL)) TypeData
,EXTRACT(fc.SequenceData, '/MEPSequenceData@LastTimeStamp').GetStringVal() seq_LastTimeStamp
,EXTRACT(fc.TypData, '/Data@LastTimeStamp').GetStringVal() typ_LastTimeStamp
FROM
tabl_order ord
,tabl_fileinfo fi
,tabl_filecontent fc
,tabl_measurementlog mlog
WHERE
mlog.orderid = ord.id
AND fi.id = fc.fileid
AND ((mlog.templateid = fi.ownerid AND mlog.templateversion = fi.additionalkey)
OR (ord.id = fi.ownerid))
GROUP BY
ord.id
, EXTRACT(fc.SequenceData, '/MEPSequenceData@LastTimeStamp').GetStringVal()
, EXTRACT(fc.TypData, '/Data@LastTimeStamp').GetStringVal()
/

clear columns

Here is the output.

ORDERID ABLAUFDATA TYPEDATA SEQ_LASTTIMESTAMP TYP_LASTTIMESTAMP
-------- -------------------- -------------------- -------------------- --------------------
259206 Typdaten.xml 20040430171532
259206 Ablaufdaten.xml 20030330181532

But when i exclude the EXTRACT function colulmns from the query i get single record.

ORDERID ABLAUFDATA TYPEDATA
---------- -------------------------------- --------------------------------
259206 Ablaufdaten.xml Typdaten.xml





But here is the output i am looking for with the EXTRACT f unction inclusive.

ORDERID ABLAUFDATA TYPEDATA SEQ_LASTTIMESTAMP TYP_LASTTIMESTAMP
-------- -------------------- -------------------- -------------------- --------------------
259206 Ablaufdaten.xml Typdaten.xml 20030330181532 20040430171532

You help will be greatly appreciated.

if you are always expecting two rows and just want to squash up the typedata/etc columns -- max them all:

ops$tkyte@ORA9IR2> SELECT
  2        ord.id OrderID
  3           ,max(decode(ownertypeid, 2, FileName, NULL)) AblaufData
  4          ,max(decode(ownertypeid, 3, FileName, NULL)) TypeData
  5          ,max(EXTRACT(fc.SequenceData, '/MEPSequenceData@LastTimeStamp').GetStringVal()) seq_LastTimeStamp
  6          ,max(EXTRACT(fc.TypData, '/Data@LastTimeStamp').GetStringVal()) typ_LastTimeStamp
  7  FROM
  8           tabl_order                ord
  9        ,tabl_fileinfo            fi
 10        ,tabl_filecontent       fc
 11        ,tabl_measurementlog    mlog
 12  WHERE
 13         mlog.orderid     = ord.id
 14    AND  fi.id            = fc.fileid
 15    AND ((mlog.templateid = fi.ownerid  AND  mlog.templateversion =
 16  fi.additionalkey)
 17     OR (ord.id           = fi.ownerid))
 18  GROUP BY ord.id
 19  /
 
 ORDERID ABLAUFDATA      TYPEDATA           SEQ_LASTTIMES TYP_LASTTIMESTAMP
-------- --------------  ------------------ -------------- ------------------
  259206 Ablaufdaten.xml Typdaten.xml      20030330181532     20040430171532
 
 

excellent as always.

Hi tom,
I have a table T with four columns, say, A1,A2,A3,A4. All are nullable columns. Now I need to write a query which will fetch the first not null column value from each record.
For eg if the table contains values as:
A1 A2 A3 A4
-- -- -- --
501 865
653 999
The query result should be:
Result
------
501
653
Is this possible through a query? Or should I need to write procedural code for this.I'm using Oracle 9i release 2.Thank you very much for your time.

no create table, no inserts... sigh.

read about coalesce()

ops$tkyte@ORA9IR2> select coalesce( null, 1 ) from dual;
 
COALESCE(NULL,1)
----------------
               1
 
ops$tkyte@ORA9IR2> select coalesce( null, null, 1, 2 ) from dual;
 
COALESCE(NULL,NULL,1,2)
-----------------------
                      1
 
ops$tkyte@ORA9IR2>
 

Tom,
I have gone through Analytics and also Pivots that are been asked and answered by you, but I still have a question.

I have a table like :

Table X
--------
TPID number(10) pk
CDID number(10)
SRCID varchar2(10)


Now there can be number of CDID associated with SRCID. For eg.
CDID SRCID
1 HT
1 FF
1 CT
2 HT
3 CT
4 FF
4 BG
...............
...............

Now I want to find out from this table in one report:

1. UNIQUE CDID for a SRCID
2. COUNT OF OVERLAPS

For eg.

SRCID CNT1(BG) CNT2(CT) CNT3(FF) CNT3(HT) UNIQUE CNT(SRCID)
BG 10 2 3 0 5
CT 15 30 5 1 9
FF 3 5 15 0 7

CNT = Counts

The columns, apart from the last UNIQUE CNT(SRCID) gives the overlapping SRCID's. The last column basically gives the UNIQUE SRCID . I think its a pivot report, or may be some analytics, am at a loss can u please help ?

Regards
Arindam

Here is an example...

create table test
(TPID number(10) primary key,
CDID number(10),
SRCID varchar2(10))

insert into test values(1,1,'AV');
insert into test values(2,1,'AV');
insert into test values(3,1,'AV');
insert into test values(4,2,'AV');
insert into test values(5,2,'HT');
insert into test values(6,2,'HT');
insert into test values(7,2,'BG');
insert into test values(8,3,'CT');
insert into test values(9,3,'CT');
insert into test values(10,3,'HT');
insert into test values(11,3,'AV');
insert into test values(12,4,'CT');
insert into test values(13,4,'FF');
insert into test values(14,4,'HT');

commit;

Now I want to find out from this table in one report:

1. UNIQUE CDID for a SRCID
2. COUNT OF OVERLAPS

Overlaps = How many (counts) src's overlap each other. For example :

number of unique CDID using 'HT' = 0
number of CDID using 'HT' but also uses 'BG' = 1 (CDID 2)
number of CDID using 'HT' but also uses 'AV' = 1 (CDID 3)
number of CDID using 'HT' but also uses 'CT' = 2 (CDID = 3 and 4 )

Its like set theory's intersection.
If we consider AV,BG,CT,HT,FF to be sets and CDID's as values for each set then we might represent the same as :
AV = {1,1,1,2,3}
BG = {2}
CT = {3,3,4}
HT = {2,2,3,4}
FF = {4}

so looking at the example given above :
Unique HT = 0
HT intersection(overlap) BG = 1
HT intersection(overlap) CT = 1 etc.

Unique FF = 0
FF intersection(overlap) AV = 0
FF intersection(overlap) BG = 0
FF intersection(overlap) CT = 1
FF intersection(overlap) HT = 1

Hope I could make myself clear.

Regards

It should be 3. Number of UNIQUE CCID for HT = 3. I apologise.

regards

Tom,
I got a query working for my earlier problem. It goes like this:
select src,
max(av) AV,
round((max(av)/max(cnt))*100,2) perAV,
max(bg) BG,
round((max(bg)/max(cnt))*100,2) perBG,
max(ct) CT,
round((max(ct)/max(cnt))*100,2) perCT,
max(ht) HT,
round((max(ht)/max(cnt))*100,2) perHT,
max(ff) FF,
round((max(ff)/max(cnt))*100,2) perFF,
max(cnt) UNQ
from
(
select vt2.srcid src,sum(AV_FLAG) AV, sum(BG_FLAG) BG,sum(CT_FLAG) CT,sum(HT_FLAG) HT,sum(FF_FLAG) FF,null cnt
from
(
select CDID,SRCID,
max(decode(srcid,'AV',1,0)) AV_FLAG,
max(decode(srcid,'BG',1,0)) BG_FLAG,
max(decode(srcid,'CT',1,0)) CT_FLAG,
max(decode(srcid,'HT',1,0)) HT_FLAG,
max(decode(srcid,'FF',1,0)) FF_FLAG
from test2
group by CDID,SRCID
) VT1
,
(
select cdid,srcid
from test2
group by srcid,cdid
) VT2
where vt1.cdid = vt2.cdid
group by vt2.srcid
union
select srcid,null,null,null,null,null,count(srcid) cnt
from test2
group by srcid
)
group by src

perAV,perBG etc gives us the percentage of a src within an overlapping (intersecting) source. Could you comment on the same ? Is there a neater way to write this query ?

Regards

Can you give me an example of Group By cube with rollup using my example ?

Regards

I want to show result as follows

Mon Tue Wed .....Sun
c1 c2 c3 c1 c2 c3 c1 c2 c3 c1 c2 c3
abcd x y z
pppp x1 y1 z1
llll x2 y2 z2
uuuu x3 y3 z3

Kind of cross table and pivot. Can we achive this in single query in Oracle 8.0

I have a query as below:
SELECT
d.SERVICE_CENTER_ID,
d.PRODUCT_LINE_CD,
NVL(e.QUARTER_NUM,2) quarter,
NVL(e.APPLICABLE_YR,2004) YEAR,
MAX(DECODE(rn,1,SURVEY_QSTN_AVG_SCR_NUM)) Accomodating_Needs,
MAX(DECODE(rn,2,SURVEY_QSTN_AVG_SCR_NUM)) Returned_Request_Date,
MAX(DECODE(rn,3,SURVEY_QSTN_AVG_SCR_NUM)) Repair_Time,
MAX(DECODE(rn,4,SURVEY_QSTN_AVG_SCR_NUM)) Shown_All_Info,
MAX(DECODE(rn,5,SURVEY_QSTN_AVG_SCR_NUM)) Web_Site_Tools,
MAX(DECODE(rn,6,SURVEY_QSTN_AVG_SCR_NUM)) Web_Site_Tools_Usability,
MAX(DECODE(rn,7,SURVEY_QSTN_AVG_SCR_NUM)) Compared_Costs,
MAX(DECODE(rn,8,SURVEY_QSTN_AVG_SCR_NUM)) Invoice_Accuracy,
MAX(DECODE(rn,9,SURVEY_QSTN_AVG_SCR_NUM)) Ease_Invoice_Disputes,
MAX(DECODE(rn,10,SURVEY_QSTN_AVG_SCR_NUM)) Appearance_Center,
MAX(DECODE(rn,11,SURVEY_QSTN_AVG_SCR_NUM)) Work_Quality,
MAX(DECODE(rn,12,SURVEY_QSTN_AVG_SCR_NUM)) Professionalism,
MAX(DECODE(rn,13,SURVEY_QSTN_AVG_SCR_NUM)) Rental_Engine_Support
FROM COMMULATIVE_SRVY_QST_AVG_SCR e,SERVICE_CNTR_PROD_LINE_AUTH d,
(SELECT a.QUESTION_NUM,a.APPLICABLE_YR,a.QUARTER_NUM,
row_number() over (PARTITION BY a.SEQUENCE_NUM ORDER BY a.QUESTION_NUM) rn
FROM STAAR_SURVEY_ANSWER a,SURVEY_QUESTION_MASTER b
WHERE b.QUESTION_TYPE_ID=1
AND a.QUESTION_NUM=b.QUESTION_NUM AND a.SURVEY_ID=1
) x
WHERE e.APPLICABLE_YR(+)=2004
AND e.QUARTER_NUM(+)=2
AND e.QUESTION_NUM=x.QUESTION_NUM
AND e.SERVICE_CENTER_ID(+)=d.SERVICE_CENTER_ID
AND e.PRODUCT_LINE_CD(+)=d.PRODUCT_LINE_CD
AND d.SERVICE_CENTER_ID IN('162','14','128')
AND d.PRODUCT_LINE_CD IN ('TFE')
GROUP BY d.SERVICE_CENTER_ID,d.PRODUCT_LINE_CD,e.QUARTER_NUM,e.APPLICABLE_YR
ORDER BY 1,2,3,4

I want to make the pivot-section of this query to be dynamic--means if i add one more question to SURVEY_QUESTION_MASTER table, then dynamically on more decode statement should be appended in above query.

Please help me at the earliest.

Nikhil from India

I couldn't get much from your response... Basically, i wnat my whole query to become dynamic,but just with the pivot section taking into consideration..

Nikhil

I have written a function to do this and it is published in
OTN, Code tip of the week in 2004. Check it out.

Here is the URL </code> https://asktom.oracle.com/magazine-archive.htm <code>

-Krishna

Here is the URL </code> https://asktom.oracle.com/magazine-archive.htm <code>

-Krishna

Hi Tom,
I used code from your book (Effective Oracle By Design) page 642. I get the error:
----
CNAME VAL
------------------------------ ----------
SCORE887 .0056
ERROR:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "SYS.DBMS_SYS_SQL", line 1149
ORA-06512: at "SYS.DBMS_SQL", line 339
ORA-06512: at "CVS.COLS_AS_ROWS", line 39
_______________
There are about 900 columns in the table.
Could you please help me for this.
Thanks

Tom, can you please post the code for COLS_AS_ROWS? Couldn't find it in ~tkyte.
Thanks.

Hi Tom,

The solution you gave for the original problem, using group ny, max, decode, is quite interesting. It's not obvious, I wouldn't have thought of it. How about:

create or replace view event_view1
as
select t_events.event,
t_events.descript,
t_events.logdate,
my_function(t_events.event, 'userid') userid,
my_function(t_events.event, 'company') company,
my_function(t_events.event, 'transaction') transaction
from t_events

And my_funtion(p_event, p_element) does a select from t_event_data with the predicate "where event = p_event and element = p_element".

How would the performance compare?

Best regards

Huy

Tom,
Thanks for great demonstration again.
can we use coalesce to convert rows to columns like in "SQL SERVER".
A piece of code that i use in sql server is like this

declare @name int
select @name = coalesce(@name + ',','') + ename from
emp ;
print @name

consider normal emp table structure in scott.emp ;

can i do the same thing in oracle also with this "coalesce". If so how ?

- Thanks in advance
ravi

sorry please read above as
declare @name varchar(2000)

Hi Tom

Hi Tom,
Thanks for this fantastic technique. I have some different table structure and wondering how can i do this?
For example:
First table: master(Prop_ID, Desc, Date)
Prop_ID Desc Date
1 f_name xxxxxxx
2 L_name zzzzzz
3 email yyyyy


Second table: detail(cust_id, prop_id, value)
Cust_id Prop_id Value
1 1 prnab
1 2 desh
1 3 pd@xyz.com
2 1 anu
2 2 bharat
2 3 ab@pmn.com
3 1 lata
3 2 sharma
3 3 ls@trp.com

and now i want to develop report like this

Cust_id f_name l_name email
1 prnab desh pd@xyz.com
2 anu bharat ab@pmn.com

hi Tom,
i tried to build the query but not succeed to get the desired result, Please help me as i need to develop same report frequently. i have attache the create table and insert statements.

create table master(prop_id number,descp varchar2(50), cr_date date);

create table detail(cust_id number(10),prop_id number, value varchar2(100);

insert into master values(1,'F_name',sysdate);
insert into master values(2,'L_name',sysdate);
insert into master values(3,'E_mail',sysdate);
insert into detail values(1,1,'prnab');
insert into detail values(1,2,'desh');
insert into detail values(1,3,'pd@asd.com');
insert into detail values(2,1,'anu');
insert into detail values(2,2,'bharat');
insert into detail values(2,3,'ab@pnm.com');
insert into detail values(3,1,'lata');
insert into detail values(3,1,'sharma');
insert into detail values(3,1,'ls@rte.com');

and now i want to develop report like this

Cust_id f_name l_name email
1 prnab desh pd@xyz.com
2 anu bharat ab@pmn.com

I tried to build the query like :
select t1.event,
max(decode(tb.event,40,element,'NA'))f_name,
max(decode(tb.event,41,element,'NA'))l_name,
max(decode(tb.event,5,element,'NA'))e_mail
from t1_dtl tb , t1
where tb.event= t1.event and
rownum <=100
group by t1.event;

Thanks and regards.

ops$tkyte%ORA9IR2> select cust_id,
  2         max(decode(prop_id,1,value)) fname,
  3         max(decode(prop_id,2,value)) lname,
  4         max(decode(prop_id,3,value)) email
  5    from detail
  6   group by cust_id;

   CUST_ID FNAME      LNAME      EMAIL
---------- ---------- ---------- ----------
         1 prnab      desh       pd@asd.com
         2 anu        bharat     ab@pnm.com
         3 sharma

ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> select cust_id,
  2         max(decode(descp,'F_name',value)) fname,
  3         max(decode(descp,'L_name',value)) lname,
  4         max(decode(descp,'E_mail',value)) email
  5    from detail, master
  6   where detail.prop_id = master.prop_id
  7   group by cust_id;

   CUST_ID FNAME      LNAME      EMAIL
---------- ---------- ---------- ----------
         1 prnab      desh       pd@asd.com
         2 anu        bharat     ab@pnm.com
         3 sharma

 

Hello Tom,

thank you for your ideas on this topic.
I have allowed myself to use your ideas to create a generalized approach:

-- Rows to columns:
SELECT "1", "2", "3", "4", "5", "6"
FROM (SELECT MAX(decode(nr, 1, val, NULL)) AS "1",
MAX(decode(nr, 2, val, NULL)) AS "2",
MAX(decode(nr, 3, val, NULL)) AS "3",
MAX(decode(nr, 4, val, NULL)) AS "4",
MAX(decode(nr, 5, val, NULL)) AS "5",
MAX(decode(nr, 6, val, NULL)) AS "6"
FROM (SELECT val, ROW_NUMBER() over(ORDER BY ord) AS nr
FROM (/***** Your query goes here: *****/
SELECT username AS val, username AS ord
FROM all_users
/*********************************/)))
GROUP BY "1", "2", "3", "4", "5", "6";


-- Columns to rows:
select decode(col, 1,"1", 2,"2", 3,"3", 4,"4") AS "Table"
from (/***** Your query goes here: *****/
SELECT owner AS "1", table_name AS "2", status AS "3",
tablespace_name AS "4"
FROM ALL_TABLES
WHERE ROWNUM = 1
/*********************************/),
(select ROWNUM AS col from all_objects WHERE ROWNUM <= 4)


Best regards,
Matthias Schulz

Hi, my problem is: i have this sql
SELECT
sdan8,
sdsrp1,
sdsrp2,
sditm,
sdmcu,
sum(sduorg)
FROM testdta.f4101,
(select *
from testdta.f4211
where sdtrdj between (select wfdtfr from F570001) and
(select wfdtto from F570001)
union all
select *
from testdta.f42119
where sdtrdj between (select wfdtfr from F570001) and
(select wfdtto from F570001)
)
WHERE
sditm=imitm
GROUP BY
sdan8, sdsrp1, sdsrp2, sditm, sdmcu


result
sdan8 sdsrp1 sdsrp2 sditm sdmcu sum(sduorg)
0 01 130 806821 A 1
0 01 130 806822 B 2
0 01 130 806824 A 4
0 01 130 806824 C 4

i need generate this output
sdan8 sdsrp1 sdspr2 sditm sdmcuA sdmcuB sdmcuC.... sdmcn
0 01 130 806821 1 0 0 0
0 01 130 806822 0 1 0 0
0 01 130 806824 4 0 4 0


thanks (oracle 9i)

Hi Tom,

We have a situation which demands the count of rows processed by some interfaces to be recorded and reporting using XML.

The autosys jobs run every hour to process data using interface. This new interface is going to count the rows processed and record it.

Is it a good idea to have the columns like hr1_count, hr2_count...hr24_count or it is recommended to use rows instead of columns with date/time stamp from where the hours or processing can be calculated and record counts will privide the result.

My experience tells it should be through columns as millions of rows will be processed with the interfaces in every hour and the 1st option will record only one row with the count for each interface run on an hourly basis and the 2nd option will actually push the same amount of rows (millions) into this new table unnecessarily - please do suggest!

Thanks,
AKumar

Hi Tom, thanks for replying...

The argument is as follows:
1. If rows, the disadvantages are
a) we are unncessarily piling up and cosuming database storage
b) To sum all those rows to find out the coung will also impact on query processing
2. If we store these columns, then we can get all these info in one row only - see the impact on query performance. Basically I am talking about 1 rows v/s million rows - query processing WILL BE FASTER

In today's world storage is not a big issue, but the performance is. With option 1, we are hitting none of this issues and with option 2, we are hitting both on the head. Please counter...

Hi Tom,

I agree with you on "for you see, as soon as you make it hourly, someone will say "lets to half hours" - or whatever" - but the requirement is clear - and the users might not also be interested in knowing how much data flows every half an hour/minute -- it's useless for them. Let's assume that they want it every hour...

I am sorry, if I am overstreching this conversation. But some results do not seem to agree with your view.

I have created two tables
1. xintf_count (INTF_NAME, DATE_TIME, PROCESS_TYPE) with 11100 rows AND
2. xintf_count_in_col (INTF_NAME, hr1_count, hr2_count...hr24_count) with 2 rows having all the information as above

(Using 10g) The explain plan for both the queries are as follows:
To access #1,
"select intf_name, to_char(date_time,'hh24'), count(1)
from xintf_count
group by intf_name, to_char(date_time,'hh24') order by 1,2"

Operation Options Object Rows Time Cost Bytes Filter Predicates * Access Predicates
SELECT STATEMENT 1 114 9,477 61
SORT GROUP BY 1 114 9,477 61
TABLE ACCESS FULL XINTF_COUNT 1 114 9,476 61

-- and this for #2,
"select * from xintf_count_in_col order by 1"

Operation Options Object Rows Time Cost Bytes Filter Predicates * Access Predicates
SELECT STATEMENT 2 1 4 728
SORT ORDER BY 2 1 4 728
TABLE ACCESS FULL XINTF_COUNT 2 1 3 728

-- I am thinking of fetch time - inserts will be done simply once but the fetch will be more time consuming...
Your thoughts please...

Hi Tom,

Yes, the design is not generic - it's different. And this design being robust, there is no counter argument to it - but the way we have been doing designs make us feel happy to go with the conventional way.

I have been working for 7 years as a designer in various systems and I do not agree with people who go design the conventional way without thinking the implications that follow on the production system. The developer will code the way the system is designed and will obviously scream that coding time is more in the non-conventional way. It's a fact that developers think little about the implications their code enforces on the database.

There will be little bit of more coding with this but eventually it will provide the same output faster and that's the intent here.

Anyways, thanks a lot Tom.

Hi Tom,

There is a saying in India (in Hindi):

"Jo Boya - so Paya" - which means "get results the way you reap".

Unfortunately, the scope of software programming is limited within a boundary. We are just silent workers ;)

- And we give them what they ask for.

Thanks!

Hi Tom,

I have the following as my base data:

SQL> CREATE TABLE tab1 (col1 NUMBER, col2 NUMBER, col3 NUMBER, Yr NUMBER)
2 /

Table created

SQL> INSERT INTO tab1 VALUES (1, 2, 3, 2004)
2 /

1 row inserted

SQL> INSERT INTO tab1 VALUES (4, 5, 6, 2005)
2 /

1 row inserted

SQL> INSERT INTO tab1 VALUES (7, 8, 9, 2006)
2 /

1 row inserted

SQL> INSERT INTO tab1 VALUES (10, 11, 12, 2007)
2 /

1 row inserted

SQL> COMMIT
2 /

Commit complete

SQL> SELECT * FROM tab1
2 /

COL1 COL2 COL3 YR
---------- ---------- ---------- ----------
1 2 3 2004
4 5 6 2005
7 8 9 2006
10 11 12 2007

SQL>

Now, my requirement is to represent the data in a tabular format such as this:

ATTR 2004 2005 2006 2007
---------- ---------- ---------- ---------- ----------
COL1 1 4 7 10
COL2 2 5 8 11
COL3 3 6 9 12

Number of 'Year columns' may grow - however, more than 40 years' of data is not expected. There are fixed number COLs (around 50).

Please help.

Also, asktom site has always been my favourite one for problem-solving and also for knwoledge building. Thanks a lot!

Best Regards,

ops$tkyte%ORA10GR2> with data
  2  as
  3  (select level l from dual connect by level <= 3)
  4  select attr,
  5         max( decode( yr, 2004, val ) ) "2004",
  6         max( decode( yr, 2005, val ) ) "2005",
  7         max( decode( yr, 2006, val ) ) "2006",
  8         max( decode( yr, 2007, val ) ) "2007"
  9    from (
 10  select decode( l, 1, 'COL1', 2, 'COL2', 3, 'COL3' ) attr,
 11         decode( l, 1, col1,   2,  col2,  3,  col3  ) val,
 12             yr
 13    from tab1, data
 14         )
 15   group by attr
 16   order by attr
 17  /

ATTR       2004       2005       2006       2007
---- ---------- ---------- ---------- ----------
COL1          1          4          7         10
COL2          2          5          8         11
COL3          3          6          9         12

I was wondering if I can you your thoughts on the following.

I have the following table:

Table:rfk_test

user_input_inst_id input_parm_type_id, user_input_parm_value
1 MODELINTID 212
1 MAKEINTID 23
1 MODELINTID 331
1 MAKEINTID 36
1 ZIPCODE 92310
2 MAKEINTID 23
2 MAKEINTID 36
2 MODELINTID 5767
2 ZIPCODE 48152

Transform rows to columns for every combination:

Output>
user_input_inst_id makeintid modelintid zipcode
1 36 331 92310
1 23 331 92310
1 36 212 92310
1 23 212 92310
2 36 5767 48152
2 23 5767 48152


I am looking for the most efficient way.

Our production table will have 16 different input_parm_types and source table (i.e. rfk_test) will contain approximately 20 million rows.

Currently we are testing with the following approach:

SELECT a.user_input_inst_id,
b.user_input_parm_value AS make_id,
c.user_input_parm_value AS model_id,
d.user_input_parm_value AS zip_code
FROM (SELECT DISTINCT user_input_inst_id
FROM rfk_test) a,
(SELECT DISTINCT user_input_inst_id,
user_input_parm_value
FROM rfk_test
WHERE UPPER (input_parm_type_id) = 'MAKEINTID') b,
(SELECT DISTINCT user_input_inst_id,
user_input_parm_value
FROM rfk_test
WHERE UPPER (input_parm_type_id) = 'MODELINTID') c,
(SELECT DISTINCT user_input_inst_id,
user_input_parm_value
FROM rfk_test
WHERE UPPER (input_parm_type_id) = 'ZIPCODE') d
WHERE a.user_input_inst_id = b.user_input_inst_id(+)
AND a.user_input_inst_id = c.user_input_inst_id(+)
AND a.user_input_inst_id = d.user_input_inst_id(+)

Thanks

I was wondering if I can you your thoughts on the following.

I have the following table:

CREATE TABLE rfk_test
    (user_input_inst_id             NUMBER(38,0),
    input_parm_type_id             VARCHAR2(250),
    user_input_parm_value          VARCHAR2(250))

/

INSERT INTO rfk_test(user_input_inst_id, input_parm_type_id, user_input_parm_value) 
values (1,'MODELINTID',212);
INSERT INTO rfk_test(user_input_inst_id, input_parm_type_id, user_input_parm_value) 
values (1,'MAKEINTID',23);
INSERT INTO rfk_test(user_input_inst_id, input_parm_type_id, user_input_parm_value) 
values (1,'MODELINTID',331);
INSERT INTO rfk_test(user_input_inst_id, input_parm_type_id, user_input_parm_value) 
values (1,'MAKEINTID',36);
INSERT INTO rfk_test(user_input_inst_id, input_parm_type_id, user_input_parm_value) 
values (1,'ZIPCODE',92310);
INSERT INTO rfk_test(user_input_inst_id, input_parm_type_id, user_input_parm_value) 
values (2,'MAKEINTID',23);
INSERT INTO rfk_test(user_input_inst_id, input_parm_type_id, user_input_parm_value) 
values (2,'MAKEINTID',36);
INSERT INTO rfk_test(user_input_inst_id, input_parm_type_id, user_input_parm_value) 
values (2,'MODELINTID',5767);
INSERT INTO rfk_test(user_input_inst_id, input_parm_type_id, user_input_parm_value) 
values (2,'ZIPCODE',48152);

Transform rows to columns for every combination:

Output>
user_input_inst_id makeintid modelintid zipcode
1 36 331 92310
1 23 331 92310
1 36 212 92310
1 23 212 92310
2 36 5767 48152
2 23 5767 48152


I am looking for the most efficient way.

Our production table will have 16 different input_parm_types and source table (i.e. rfk_test) will contain approximately 20 million rows.

Currently we are testing with the following approach:

SELECT a.user_input_inst_id,
    b.user_input_parm_value AS make_id,
    c.user_input_parm_value AS model_id,
    d.user_input_parm_value AS zip_code
FROM 
(SELECT DISTINCT user_input_inst_id FROM rfk_test) a,
(SELECT DISTINCT user_input_inst_id,
                 user_input_parm_value
   FROM rfk_test
  WHERE UPPER (input_parm_type_id) = 'MAKEINTID') b,
(SELECT DISTINCT user_input_inst_id,
                 user_input_parm_value
   FROM rfk_test
  WHERE UPPER (input_parm_type_id) = 'MODELINTID') c,
(SELECT DISTINCT user_input_inst_id,
        user_input_parm_value
   FROM rfk_test
  WHERE UPPER (input_parm_type_id) = 'ZIPCODE') d
WHERE a.user_input_inst_id = b.user_input_inst_id(+)
  AND a.user_input_inst_id = c.user_input_inst_id(+)
  AND a.user_input_inst_id = d.user_input_inst_id(+)

Thanks

ops$tkyte%ORA10GR2> select user_input_inst_id,
  2         max( decode( input_parm_type_id, 'MODELINTID', user_input_parm_value ) ) MODELINTID,
  3         max( decode( input_parm_type_id, 'MAKEINTID', user_input_parm_value ) ) MAKEINTID,
  4         max( decode( input_parm_type_id, 'ZIPCODE', user_input_parm_value ) ) ZIPCODE
  5    from rfk_test
  6   group by user_input_inst_id
  7  /

USER_INPUT_INST_ID MODELINTID               MAKEINTID                ZIPCODE
------------------ ------------------------ ------------------------ ------------------------
                 1 331                      36                       92310
                 2 5767                     36                       48152

Your solution does not give my the results I am looking for. I need to return every combination for each user_input_inst_id.

The result set should look like:

user_input_inst_id makeintid modelintid zipcode
1                  36        331        92310
1                  23        331        92310
1                  36        212        92310
1                  23        212        92310
2                  36        5767       48152
2                  23        5767       48152 

There are multiple makeintid and modelintid for a given user_input_inst_id.

Are you referring to how the source data is modeled?

Thanks

Hi Tom,

You remember "Garbage in - Garbage out" ?

Well, the situation has finally arrived. The user now wants to have the count of the interface records processed after every 30 minutes.

As anybody might have perceived the design was not that worse but was skillfully handled! Any idea how I managed reporting the count for every 30 mins, with the design recording one hour count? That's easy.

Thanks, AKumar

Tom,

I'm running Oracle 10.2.0.3 Standard Edition.

I have a bit of a twist on the "rows to columns" that I cannot figure out.
I've got a time entry table with rows containing employee time by project.

Here is a simple example (we also have a time_id column fed by a sequence, which I'm omitting as it does not seem relevant)

    create table time_demo (
        emp_id    number,
        proj_id   number,
        work_date date,
        hours     number(5,2)
    )

    And some data:

        insert into time_demo values (1,1,to_date('4/2/2007','MM/DD/YYYY'),4.25);
        insert into time_demo values (1,1,to_date('4/2/2007','MM/DD/YYYY'),1.50);
        insert into time_demo values (1,1,to_date('4/2/2007','MM/DD/YYYY'),0.75);
        insert into time_demo values (1,1,to_date('4/3/2007','MM/DD/YYYY'),7.25);
        insert into time_demo values (1,1,to_date('4/4/2007','MM/DD/YYYY'),6.50);
        insert into time_demo values (1,1,to_date('4/5/2007','MM/DD/YYYY'),4.25);
        insert into time_demo values (1,1,to_date('4/6/2007','MM/DD/YYYY'),7.75);

    Now, I use the decode method to transform rows to columns to get a report by week:

    select emp_id, proj_id,
       decode(to_char(work_date,'D'),1,hours,null) sun,
       decode(to_char(work_date,'D'),2,hours,null) mon,
       decode(to_char(work_date,'D'),3,hours,null) tue,
       decode(to_char(work_date,'D'),4,hours,null) wed,
       decode(to_char(work_date,'D'),5,hours,null) thu,
       decode(to_char(work_date,'D'),6,hours,null) fri,
       decode(to_char(work_date,'D'),7,hours,null) sat
    from   time_demo

    I am intentionally leaving out the "sum()" and "group by" so 
    that I can see each entry.  I get this:

         EMP_ID PROJ_ID    SUN    MON    TUE    WED    THU    FRI    SAT
        ------- ------- ------ ------ ------ ------ ------ ------ ------
              1       1          4.25
              1       1          1.50
              1       1           .75
              1       1                 7.25
              1       1                        6.50
              1       1                               4.25
              1       1                                      7.75

        7 rows selected.
        SQL>                                     

    What really I want for output is this:

        EMP_ID PROJ_ID    SUN    MON    TUE    WED    THU    FRI    SAT
        ------- ------- ------ ------ ------ ------ ------ ------ ------
              1       1          4.25   7.25   6.50   4.25   7.75  
              1       1          1.50
              1       1           .75

In other words, consolidate the entries into as few rows as possible, but keep all individual entries. I don't even care what order the entries for Monday are in, just that I see all of them. The other columns in rows two and three should return NULL's.

I case you are wondering why I want to do this, I have an Application Express app and I want to see all individual entries displayed, but save screen real estate to fit as many rows as possible.

My thought is to write a pipelined function to return the rows the way that I want them, but it really seems like there should be a "SQL way" to do this.

Any help would be appreciated.

Thanks,
David

First thing i'll say is i wouldn't use this is real code :)

The solution leaves out project (easy enough to add in).

I'm certain it can be refined (or done better-er outright), but this gives the answer...


--wanted a second employee to test against
insert into time_demo values (2,1,to_date('4/1/2007','MM/DD/YYYY'),4.3);
insert into time_demo values (2,1,to_date('4/3/2007','MM/DD/YYYY'),6.9);


WITH step_one AS
(
SELECT
emp_id,
TO_CHAR(work_date,'D') AS day,
hours
FROM time_demo
ORDER BY emp_id, TO_CHAR(work_date,'D'), hours
),
step_two AS
(
SELECT
emp_id,
day,
hours,
ROW_NUMBER() OVER (PARTITION BY emp_id, day ORDER BY 1) AS rownumber
FROM STEP_ONE
),
DRIVER AS
(
SELECT
DISTINCT
emp_id,
rownumber
FROM step_two
)
SELECT
d.emp_id,
s1.hours AS sun,
s2.hours AS mon,
s3.hours AS tue,
s4.hours AS wed,
s5.hours AS thu,
s6.hours AS fri,
s7.hours AS sat
FROM
DRIVER d,
(
SELECT s.rownumber, s.hours, s.emp_id FROM STEP_TWO S, DRIVER D
WHERE S.rownumber = d.rownumber AND s.DAY = 1 AND s.emp_id = d.emp_id
) S1,
(
SELECT s.rownumber, s.hours, s.emp_id FROM STEP_TWO S, DRIVER D
WHERE S.rownumber = d.rownumber AND s.DAY = 2 AND s.emp_id = d.emp_id
) S2,
(
SELECT s.rownumber, s.hours, s.emp_id FROM STEP_TWO S, DRIVER D
WHERE S.rownumber = d.rownumber AND s.DAY = 3 AND s.emp_id = d.emp_id
) S3,
(
SELECT s.rownumber, s.hours, s.emp_id FROM STEP_TWO S, DRIVER D
WHERE S.rownumber = d.rownumber AND s.DAY = 4 AND s.emp_id = d.emp_id
) S4,
(
SELECT s.rownumber, s.hours, s.emp_id FROM STEP_TWO S, DRIVER D
WHERE S.rownumber = d.rownumber AND s.DAY = 5 AND s.emp_id = d.emp_id
) S5,
(
SELECT s.rownumber, s.hours, s.emp_id FROM STEP_TWO S, DRIVER D
WHERE S.rownumber = d.rownumber AND s.DAY = 6 AND s.emp_id = d.emp_id
) S6,
(
SELECT s.rownumber, s.hours, s.emp_id FROM STEP_TWO S, DRIVER D
WHERE S.rownumber = d.rownumber AND s.DAY = 7 AND s.emp_id = d.emp_id
) S7
WHERE d.rownumber = s1.rownumber (+) AND d.emp_id = s1.emp_id (+)
AND d.rownumber = s2.rownumber (+) AND d.emp_id = s2.emp_id (+)
AND d.rownumber = s3.rownumber (+) AND d.emp_id = s3.emp_id (+)
AND d.rownumber = s4.rownumber (+) AND d.emp_id = s4.emp_id (+)
AND d.rownumber = s5.rownumber (+) AND d.emp_id = s5.emp_id (+)
AND d.rownumber = s6.rownumber (+) AND d.emp_id = s6.emp_id (+)
AND d.rownumber = s7.rownumber (+) AND d.emp_id = s7.emp_id (+);

Something simpler without self joins (Tom, sorry, when I saw the other solution I couldn't resist...)

WITH step1 AS (
SELECT emp_id, proj_id, to_char(work_date, 'D') AS daynum, HOURS FROM time_demo
), step2 AS (
SELECT step1.*,
row_number() over(PARTITION BY emp_id, proj_id, daynum ORDER BY daynum) AS rn
FROM step1
)
SELECT emp_id, proj_id, rn, 
MAX(decode(daynum, 1, HOURS, NULL)) AS sun, 
MAX(decode(daynum, 2, HOURS, NULL)) AS mon, 
MAX(decode(daynum, 3, HOURS, NULL)) AS tue, 
MAX(decode(daynum, 4, HOURS, NULL)) AS wed, 
MAX(decode(daynum, 5, HOURS, NULL)) AS thu, 
MAX(decode(daynum, 6, HOURS, NULL)) AS fri, 
MAX(decode(daynum, 7, HOURS, NULL)) AS sat 
FROM step2 
GROUP BY emp_id, proj_id, rn 
ORDER BY emp_id, proj_id, rn;

EMP_ID  PROJ_ID RN  SUN   MON   TUE  WED   THU   FRI   SAT
------- ------- --- ----- ----- ---- ----- ----- ----- ----
1       1       1         4,25  7,25  6,5  4,25  7,75
1       1       2         1,5
1       1       3         0,75

3 rows selected

Stew & Tyler, thanks for your solutions. I can see that I need to learn more about the "WITH" syntax. This is exactly what I needed.

David

Tom,
I have a requirement to transform row data into columns, where each row is actually a set of columns from the original table. The following are the create and insert statements.

create table t1 (id varchar2(1), fname1 varchar2(10), lname1 varchar2(10), id1 varchar2(10), fname2 varchar2(10), lname2 varchar2(10), id2 varchar2(10), fname3 varchar2(10), lname3 varchar2(10), id3 varchar2(10));
  
  insert into t1 values ('x', 'first1', 'last1', 'num1', 'first2', 'last2', 'num2', 'first3', 'last3', 'num3');
  
  I need the output as follows:
  
  id    col1   col2   col3
  --------------------------------
  x     first1  last1   num1
  x     first2  last2   num2
  x     first3  last3   num3

I tried the following query (from one of your previous answers, but don't seem to quite get it).

select decode(r, 1, fname1, 2, lname1, 3, id1),
       decode(r, 1, fname2, 2, lname2, 3, id2),
    decode(r, 1, fname3, 2, lname3, 3, id3)
  from (select * from t1), (select 1 r from dual union all select 2 r from dual union all select 3 r from dual)

Can you point out the right approach/query please.

Thanks a ton!

That hint worked! I guess it was right in the face, but didn't realize what I was doing until you pointed it out!

Thank you so much!

Hi Tom, Below is what I am trying to achieve.

I would appreciate any help.

Thanks in advance.

create table t13(rn number, rec_type varchar2(20));


insert into t13 values(1, '01');
insert into t13 values(2, '02');
insert into t13 values(3, '03');
insert into t13 values(4, '88');
insert into t13 values(5, '88');
insert into t13 values(6, '88');
insert into t13 values(7, '88');
insert into t13 values(8, '16');
insert into t13 values(9, '88');
insert into t13 values(10, '88');
insert into t13 values(11, '88');
insert into t13 values(12, '16');
insert into t13 values(13, '88');
insert into t13 values(14, '88');
insert into t13 values(15, '88');
insert into t13 values(16, '16');
insert into t13 values(17, '88');
insert into t13 values(18, '49');
insert into t13 values(19, '98');
insert into t13 values(20, '99');




select * From t13;

RN RE
---- --
1 01
2 02
3 03
4 88
5 88
6 88
7 88
8 16
9 88
10 88
11 88

RN RE
---- --
12 16
13 88
14 88
15 88
16 16
17 88
18 49
19 98
20 99


what I am trying to do is to append all 88's to the previous record where 88 starts.

result will look like


RN RE
---- --
1 01
2 02
3 03-88-88-88-88
8 16-88-88-88
12 16-88-88-88
16 16-88
18 49
19 98
20 99

ops$tkyte%ORA10GR2> select max( case when rec_type<>'88' then rn end ) rn,
  2         max( case when rec_type<>'88' then rec_type end ) ||
  3             replace( rpad( '*', count(*)-1, '*' ), '*', '-88' ) rec_type
  4    from (
  5  select rn, rec_type, max(grp) over (order by rn) maxgrp
  6    from (
  7  select rn, rec_type,
  8         case when rec_type <> '88'
  9                  then row_number() over (order by rn)
 10                  end grp
 11    from t13
 12         )
 13         )
 14   group by maxgrp
 15   order by rn
 16  /

        RN REC_TYPE
---------- --------------------
         1 01
         2 02
         3 03-88-88-88-88
         8 16-88-88-88
        12 16-88-88-88
        16 16-88
        18 49
        19 98
        20 99

9 rows selected.

Its amazing as always.

Thanks very much Tom

I have the following as my base data: 

SQL> CREATE TABLE tab1 (Yr NUMBER,AMOUNT NUMBER) 
2 / 

Table created 

SQL> INSERT INTO tab1 VALUES (01.12.2006,5395) 
2 / 

1 row inserted 

SQL> INSERT INTO tab1 VALUES (01.02.2007,5395) 
2 / 

1 row inserted 

SQL> INSERT INTO tab1 VALUES (01.11.2006,5395) 
2 / 

1 row inserted 

SQL> COMMIT 
2 / 

Commit complete 

SQL> SELECT * FROM tab1 
2 / 

   Yr     AMOUNT
---------- ---------- 
01.12.2006   5395     
01.02.2007   5395     
01.11.2006   5395     
    

SQL> 

Now, my requirement is to represent the data in a tabular format such as this: 

MONTH         JAN  FEB MAR APR MAY JUN JUL AUG SEP OCT  NOV  DEC
01-DEC-2006   0    0   0   0   0   0   0   0   0   0    0   5395
01-FEB-2007   0   5395 0   0   0   0   0   0   0   0    0    0
01-NOV-2006   0    0   0   0   0   0   0   0   0   0   5395  0

ops$tkyte%ORA10GR2> CREATE TABLE tab1 (Yr NUMBER,AMOUNT NUMBER)
  2  /

Table created.

ops$tkyte%ORA10GR2> INSERT INTO tab1 VALUES (01.12.2006,5395)
  2  /
INSERT INTO tab1 VALUES (01.12.2006,5395)
                              *
ERROR at line 1:
ORA-00917: missing comma

hi tom
iam extremely sorry for the thing i have done.Actually i was in tension mood that i couldn't get the result what i want.So,i misplaced the query.Please excuse me for that and please solve the problem.

sql>CREATE TABLE tab1 (Yr date,AMOUNT NUMBER)
table created

sql>INSERT INTO tab1 VALUES ('01-dec-2006',5395)
/
1 row created.

sql>INSERT INTO tab1 VALUES ('01-feb-2007',5395)
/
1 row created.

sql>INSERT INTO tab1 VALUES ('01-nov-2006',5395)
/
1 row created.

sql>COMMIT
/
Commit complete.

sql>SELECT * FROM tab1
/


YR AMOUNT
--------- --------
01-DEC-06 5395
01-FEB-07 5395
01-NOV-06 5395

sql>

Now, my requirement is to represent the data in a tabular format such as this:

MONTH JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC
01-DEC-2006 0 0 0 0 0 0 0 0 0 0 0 5395
01-FEB-2007 0 5395 0 0 0 0 0 0 0 0 0 0
01-NOV-2006 0 0 0 0 0 0 0 0 0 0 5395 0

Please look into that Tom and help me.Once again iam sorry for that.
thanking you Tom.

ops$tkyte%ORA10GR2> select yr,
  2         decode( to_char(yr,'mm'), '01', amount,0 ) jan,
  3         decode( to_char(yr,'mm'), '02', amount,0 ) feb,
  4         decode( to_char(yr,'mm'), '03', amount,0 ) mar,
  5         decode( to_char(yr,'mm'), '04', amount,0 ) apr,
  6         decode( to_char(yr,'mm'), '05', amount,0 ) may,
  7         decode( to_char(yr,'mm'), '06', amount,0 ) jun,
  8         decode( to_char(yr,'mm'), '07', amount,0 ) jul,
  9         decode( to_char(yr,'mm'), '08', amount,0 ) aug,
 10         decode( to_char(yr,'mm'), '09', amount,0 ) sep,
 11         decode( to_char(yr,'mm'), '10', amount,0 ) oct,
 12         decode( to_char(yr,'mm'), '11', amount,0 ) nov,
 13         decode( to_char(yr,'mm'), '12', amount,0 ) dec
 14    from tab1
 15   order by yr
 16  /

YR          JAN   FEB   MAR   APR   MAY   JUN   JUL   AUG   SEP   OCT   NOV   DEC
--------- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- -----
01-NOV-06     0     0     0     0     0     0     0     0     0     0  5395     0
01-DEC-06     0     0     0     0     0     0     0     0     0     0     0  5395
01-FEB-07     0  5395     0     0     0     0     0     0     0     0     0     0

ops$tkyte%ORA10GR2> select yr,
  2         sum(decode( to_char(yr,'mm'), '01', amount,0 )) jan,
  3         sum(decode( to_char(yr,'mm'), '02', amount,0 )) feb,
  4         sum(decode( to_char(yr,'mm'), '03', amount,0 )) mar,
  5         sum(decode( to_char(yr,'mm'), '04', amount,0 )) apr,
  6         sum(decode( to_char(yr,'mm'), '05', amount,0 )) may,
  7         sum(decode( to_char(yr,'mm'), '06', amount,0 )) jun,
  8         sum(decode( to_char(yr,'mm'), '07', amount,0 )) jul,
  9         sum(decode( to_char(yr,'mm'), '08', amount,0 )) aug,
 10         sum(decode( to_char(yr,'mm'), '09', amount,0 )) sep,
 11         sum(decode( to_char(yr,'mm'), '10', amount,0 )) oct,
 12         sum(decode( to_char(yr,'mm'), '11', amount,0 )) nov,
 13         sum(decode( to_char(yr,'mm'), '12', amount,0 )) dec
 14    from tab1
 15   group by yr
 16   order by yr
 17  /

YR          JAN   FEB   MAR   APR   MAY   JUN   JUL   AUG   SEP   OCT   NOV   DEC
--------- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- -----
01-NOV-06     0     0     0     0     0     0     0     0     0     0  5395     0
01-DEC-06     0     0     0     0     0     0     0     0     0     0     0  5395
01-FEB-07     0  5395     0     0     0     0     0     0     0     0     0     0

How about query like this? 

SQL> SELECT yr,
  2   case when to_char(YR,'MM') = '02' then sum(amount) else 0 end Jan,
  3   case when to_char(YR,'MM') = '02' then sum(amount) else 0 end Feb,
  4   case when to_char(YR,'MM') = '03' then sum(amount) else 0 end Mar,
  5   case when to_char(YR,'MM') = '04' then sum(amount) else 0 end Apr,
  6   case when to_char(YR,'MM') = '05' then sum(amount) else 0 end May,
  7   case when to_char(YR,'MM') = '06' then sum(amount) else 0 end Jun,
  8   case when to_char(YR,'MM') = '07' then sum(amount) else 0 end Jul,
  9   case when to_char(YR,'MM') = '08' then sum(amount) else 0 end Aug,
 10   case when to_char(YR,'MM') = '09' then sum(amount) else 0 end Sep,
 11   case when to_char(YR,'MM') = '10' then sum(amount) else 0 end Oct,
 12   case when to_char(YR,'MM') = '11' then sum(amount) else 0 end Nov,
 13   case when to_char(YR,'MM') = '12' then sum(amount) else 0 end Dec
 14    FROM tab1
 15   group by yr
 16  /

YR               JAN        FEB        MAR        APR        MAY        JUN
--------- ---------- ---------- ---------- ---------- ---------- ----------
       JUL        AUG        SEP        OCT        NOV        DEC
---------- ---------- ---------- ---------- ---------- ----------
01-NOV-06          0          0          0          0          0          0
         0          0          0          0       5395          0

01-FEB-07       5395       5395          0          0          0          0
         0          0          0          0          0          0

01-DEC-06          0          0          0          0          0          0
         0          0          0          0          0       5395

small error in my above query for month of Jan. It should be modified like this

case when to_char(YR,'MM') = '01' then sum(amount) else 0 end Jan

from
case when to_char(YR,'MM') = '02' then sum(amount) else 0 end Jan

sorry for the copy paste error

Hi,

I've got the following table:
USER_ID LVL_ID VAL_ID
CSMCF Person CSMCF
CSMCF Function GMO
CSMCF Location THA
CSMCF Access Right RW
CSMCF Person CSMCF
CSMCF Function TRIAL
CSMCF Location THA
CSMCF Access Right R
MBSPJ Person MBSPJ
MBSPJ Function GMO
MBSPJ Location IND
MBSPJ Access Right RW

And like to tranform it as
Person Location GMO TRIAL
CSMCF THA RW R
MBSPJ IND RW (NULL)

May you help me?

Cheers Sebastien

MBSPJ    Person         MBSPJ
MBSPJ    Function       GMO
MBSPJ    Location       IND
MBSPJ    Access Right   RW

to
Person  Location  GMO  TRIAL
------- --------  ----  -----
MBSPJ   IND       RW   (NULL)

I've a situation where I'm retreiving the data in a subquery, the result for which can be multiple rows and columns.

And I want the result for this sub-query to appear in a single row for the parent query.

E.g. The Result from Subquery
No. - Name
1 - Google
2 - Yahoo
3 - MSN

The transformation for the parent query
List of Names
1 - Google, 2 - Yahoo, 3 - MSN

I know it won't make much sense but the data from subquery can go from 0 to 50 records for each parent query, so to keep it simple and understandable, I want it this way.

Sorry for the mistake, but the current query is not a subquery. I thought that the expected result could come using subquery.

It's just a join statement returning multiple rows based on the relation ship, which is the expected behaviour

Currently, I'm not in an environment where I can write the create/update queries so if there is any mistake in the below statements, kindly correct me. Also I'm not an expert at this.

Also these statements are just for the sample, for a join of 2 tables. My actual statement comprises join of 5 tables.

Create Table:-
------------------
CREATE TABLE SITE_CAT(
CATID NUMBER(1),
CATEGORY VARCHAR2(10));

CREATE TABLE FAV_SITE(
SITEID NUMBER(1),
CATID NUMBER(1),
SITE_NAME VARCHAR2(10));

Insert Statements:-
------------------------
INSERT INTO SITE_CAT VALUES(1,'SEARCH');
INSERT INTO SITE_CAT VALUES(2,'EMAIL');
INSERT INTO SITE_CAT VALUES(3,'TRAVEL');

INSERT INTO FAV_SITE VALUES(1,1,'GOOGLE');
INSERT INTO FAV_SITE VALUES(2,1,'YAHOO');
INSERT INTO FAV_SITE VALUES(3,1,'MSN');

INSERT INTO FAV_SITE VALUES(1,2,'GMAIL');
INSERT INTO FAV_SITE VALUES(2,2,'YAHOO');
INSERT INTO FAV_SITE VALUES(3,2,'HOTMAIL');

INSERT INTO FAV_SITE VALUES(1,3,'TRAVELGURU');
INSERT INTO FAV_SITE VALUES(2,3,'YATRA');
INSERT INTO FAV_SITE VALUES(3,3,'INCREDIBLEINDIA');

Current Query:-
------------------
SELECT a.category
, b.siteid + " - " b.site_name as "Sites List"
from site_cat a
, fav_site b
where a.catid = b.catid
order
by a.category
, b.site_name

Current Result:-
----------------
CATEROGY Sites List
SEARCH 1 - GOOGLE
SEARCH 2 - YAHOO
SEARCH 3 - MSN
EMAIL 1 - GMAIL
EMAIL 2 - YAHOO
EMAIL 3 - HOTMAIL
TRAVEL 1 - TRAVELGURU
TRAVEL 2 - YATRA
TRAVEL 3 - INCREDIBLEINDIA

Expected new result:-
----------------------
CATEROGY Sites List
SEARCH 1 - GOOGLE, 2 - YAHOO, 3 - MSN
EMAIL 1 - GMAIL, 2 - YAHOO, 3 - HOTMAIL
TRAVEL 1 - TRAVELGURU, 2 - YATRA, 3 - INCREDIBLEINDIA

ops$tkyte%ORA10GR2> select category, substr(max(sys_connect_by_path( siteid || ' - ' || site_name, ', ' )),2) site_list  2    from (
  3  SELECT a.category , b.siteid,  b.site_name
  4    from site_cat a
  5       , fav_site b
  6   where a.catid = b.catid
  7   order
  8      by a.category
  9       , b.site_name
 10           )
 11  start with siteid = 1 connect by prior category = category and prior siteid+1 = siteid
 12  group by category
 13  /

CATEGORY   SITE_LIST
---------- --------------------------------------------------
EMAIL       1 - GMAIL, 2 - YAHOO, 3 - HOTMAIL
TRAVEL      1 - TRAVELGURU, 2 - YATRA, 3 - INCREDIBLE
SEARCH      1 - GOOGLE, 2 - YAHOO, 3 - MSN

Hi,

Yes, I was an SQL Server programmer, but now on Oracle Apps, so only select queries for me :)
I missed on " and + because I use VB more than Oracle

Well, thanks for the query, it worked as per the requirement.

But still few problems which I don't know if it can be solved
1] Siteid will not always start from 1, it can be any number.
2] the difference in siteid's may not always be of 1, it can be far apart.

Any more suggestions for these problems.

And for oraclexe, I'd used it long ago.
Will give a try once again.

ops$tkyte%ORA10GR2> select category, substr(max(sys_connect_by_path( siteid || ' - ' || site_name, ', ' )),2) site_list
  2    from (
  3  SELECT a.category , row_number() over (partition by a.category order by b.siteid) rn, b.siteid,  b.site_name
  4    from site_cat a
  5       , fav_site b
  6   where a.catid = b.catid
  7   order
  8      by a.category
  9       , b.site_name
 10           )
 11  start with rn = 1 connect by prior category = category and prior rn+1 = rn
 12  group by category
 13  /

CATEGORY
----------
SITE_LIST
-------------------------------------------------------------------------------
EMAIL
 1 - GMAIL, 2 - YAHOO, 3 - HOTMAIL

TRAVEL
 1 - TRAVELGURU, 2 - YATRA, 3 - INCREDIBLEINDIA

SEARCH
 10 - GOOGLE, 20 - YAHOO, 30 - MSN

Thanks for the last query.
That solved my problem.

It worked like a charm :)

Hi Tom,

I have the following scenario:

create table table1
(ID varchar(1),
Desc varchar(3));

insert into table1 values('1','aaa');
insert into table1 values('2','bbb');
insert into table1 values('3','ccc');
insert into table1 values('4','ddd');
insert into table1 values('5','eee');

Table 1

ID Desc
-- ----
1 aaa
2 bbb
3 ccc
4 ddd
5 eee

create table table2
(ID varchar(1),
Key varchar(5),
Value varchar(1));

insert into table2 values('1', 'usr1', '1');
insert into table2 values('2', 'usr1', '1');
insert into table2 values('3', 'usr1', '1');
insert into table2 values('2', 'usr2', '1');
insert into table2 values('3', 'usr2', '0');
insert into table2 values('1', 'usr3', '1');
insert into table2 values('2', 'usr3', '1');
insert into table2 values('3', 'usr3', '0');
insert into table2 values('4', 'usr3', '1');
insert into table2 values('5', 'usr3', '0');

Table 2

ID Key Value
-- ---- -----
1 usr1 1
2 usr1 1
3 usr1 1
2 usr2 1
3 usr2 0
1 usr3 1
2 usr3 1
3 usr3 0
4 usr3 1
5 usr3 0

I would like to display all Table 1 left join with Table 2. So the results would be like this:

Key aaa bbb ccc ddd eee
--- --- --- --- --- ---
usr1 1 1 1 NULL NULL
usr2 NULL 1 0 NULL NULL
usr3 1 1 0 1 0

The Desc value in Table 1 needs to become the column header of the result.

Rows in Table 1 is not fixed, I have similar tables with different number of rows.

Can you help to do a query?

Thanks in advance.

Hi Tom,

Sorry for the late reply. Merry Christmas !

Thanks for the clarification. I guess the option to have dynamic number of cols can't be done in one single query.

>>(IF YOU WOULD HAVE RUN YOUR EXAMPLE, you would know that desc cannot be a column name...)

This is what I would like to achieve if there's a query that allows this.

Anyway, thanks again for the explanation. I appreciate this.

create table tst
( confirm_num number
,start_dt date
,end_dt date
)

insert into tst values (573, to_date(20071201,'yyyymmdd'), to_date(20071207,'yyyymmdd'));

insert into tst values (543, to_date(20071215,'yyyymmdd'), to_date(20071219,'yyyymmdd'));

select confirm_num
,start_dt
,end_dt
,(end_dt - start_dt) num_days
from tst;

CONFIRM_NUM START_DT END_DT NUM_DAYS
573 12/1/2007 12/7/2007 6
543 12/15/2007 12/19/2007 4


how to get the result as shown below -

CONFIRM_NUM START_DT DAY_NUM
573 12/1/2007 1
573 12/2/2007 2
573 12/3/2007 3
573 12/4/2007 4
573 12/5/2007 5
573 12/6/2007 6
543 12/15/2007 1
543 12/16/2007 2
543 12/17/2007 3
543 12/18/2007 4

ops$tkyte%ORA11GR2> select tst.*, start_dt+column_value-1, column_value
  2    from tst, table(
  3                cast(
  4                   multiset(select level from dual connect by level <= tst.end_dt-tst.start_dt)
  5                     as sys.odcinumberlist
  6                    )
  7                   )
  8  /

CONFIRM_NUM START_DT  END_DT    START_DT+ COLUMN_VALUE
----------- --------- --------- --------- ------------
        573 01-DEC-07 07-DEC-07 01-DEC-07            1
        573 01-DEC-07 07-DEC-07 02-DEC-07            2
        573 01-DEC-07 07-DEC-07 03-DEC-07            3
        573 01-DEC-07 07-DEC-07 04-DEC-07            4
        573 01-DEC-07 07-DEC-07 05-DEC-07            5
        573 01-DEC-07 07-DEC-07 06-DEC-07            6
        543 15-DEC-07 19-DEC-07 15-DEC-07            1
        543 15-DEC-07 19-DEC-07 16-DEC-07            2
        543 15-DEC-07 19-DEC-07 17-DEC-07            3
        543 15-DEC-07 19-DEC-07 18-DEC-07            4

10 rows selected.

hi,

i need something information

i have table like a .
in my table like this

a
...........
uytr12
wesd
dreddd

so now i want to find out
only string values
like (wesd
dreddd) only

not like (uytr12)

how can i

pls give me query pls...........

ops$tkyte%ORA11GR2> create table t as
  2  select 'a' txt from dual union all
  3  select 'ufadf12' txt from dual union all
  4  select 'wesd' txt from dual union all
  5  select 'dredd' txt from dual union all
  6  select '123' txt from dual;

Table created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> select * from t where replace( translate( lower(txt), 'abcdefghijklmnopqrstuvwxyz', 'a' ), 'a', '' ) is null;

TXT
-------
a
wesd
dredd

hi tom,

create table t (Stu_id varchar2(20), sem_id varchar2(20));

insert into t values(a1,b2);
insert into t values(a3,b4);
insert into t values(a5,b6);
insert into t values(a7,b8);
insert into t values(a9,b10);
insert into t values(a11,b12);
insert into t values(a13,b14);
insert into t values(a15,b16);
commit;

i want the output to be as follow:

col1 col2 col3 col4 col5 col6 col7 col8
---- ---- ---- ---- ---- ---- ---- ----
a1 b2 a3 b4 a5 b6 a7 b8
a9 b10 a11 b12 a13 b14 a15 b16

and so on?

ops$tkyte%ORA11GR2> select max(decode( col, 1, stu_id)) col1,
  2         max(decode( col, 1, sem_id)) col2,
  3         max(decode( col, 2, stu_id)) col3,
  4         max(decode( col, 2, sem_id)) col4,
  5         max(decode( col, 3, stu_id)) col5,
  6         max(decode( col, 3, sem_id)) col6,
  7         max(decode( col, 0, stu_id)) col7,
  8         max(decode( col, 0, sem_id)) col8
  9    from (
 10  select stu_id, sem_id, mod(rn,4) col, trunc((rn-0.1)/4) grp
 11    from (
 12  select stu_id, sem_id, row_number() over (order by to_number( substr( stu_id, 2 ) )) rn
 13    from t
 14         )
 15         )
 16   group by grp
 17   order by grp
 18  /

COL1 COL2 COL3 COL4 COL5 COL6 COL7 COL8
---- ---- ---- ---- ---- ---- ---- ----
a1   b2   a3   b4   a5   b6   a7   b8
a9   b10  a11  b12  a13  b14  a15  b16

Hi Tom,

need to convert rows to columns but cannot use the decode for this as it has a group by columns.

My query is
select activity_name, processing_stage,Status, count(*) from soa_message_tracking_hdr
where business_process_id=58 and trunc(creation_date)='05-MAY-11'
group by activity_name, processing_stage,status
order by activity_name, processing_stage,status


And the results are

Activity Name Processing Stage Status Count
Compliance Service SC End 114
Compliance Service SC Start 114
EmployeeCheckBpel SC End 114
EmployeeCheckBpel SC Error 3
EmployeeCheckBpel SC Start 117

Now i want something like

Activity Name Processing Stage Start Count Error Count End Count
Compliance Service SC 114 0 114
EmployeeCheckBpel SC 117 3 114


Can this be achieved?

Hi Tom,

need to convert rows to columns without using the decode.

I have created a temporary table & data and applying the logic as below:
create table #temp(
id int,
xdate date
)
insert into #temp values(1,'20-jun-1995')
insert into #temp values(2,'01-mar-1997')
insert into #temp values(3,'19-aug-2001')
insert into #temp values(4,'11-feb-1997')
insert into #temp values(5,'27-mar-2003')
insert into #temp values(6,'05-jan-1999')

select distinct 'xdate' as id,
case when id = 1 then (select top 1 xdate from #temp order by xdate asc)end as '1',
case when id = 1 then (select top 1 xdate from(select top 2 xdate from #temp order by xdate asc) #temp
order by xdate desc) end as '2',
case when id = 1 then (select top 1 xdate from(select top 3 xdate from #temp order by xdate asc) #temp
order by xdate desc) end as '3',
case when id = 1 then (select top 1 xdate from(select top 4 xdate from #temp order by xdate asc) #temp
order by xdate desc) end as '4',
case when id = 1 then (select top 1 xdate from(select top 5 xdate from #temp order by xdate asc) #temp
order by xdate desc) end as '5',
case when id = 1 then (select top 1 xdate from(select top 6 xdate from #temp order by xdate asc) #temp
order by xdate desc) end as '6'
from #temp

ops$tkyte@ORA8I.WORLD> create or replace view event_view1
  2  as
  3  select t_events.event,     t_events.descript,  t_events.logdate,
  4         max( case when element = 'userid' then curval end ) userid,
  5         max( case when element = 'company' then curval end ) company,
  6         max( case when element = 'transaction' then curval end) transaction
  7    from t_events, t_event_data
  8   where t_events.event = t_event_data.event
  9   group by t_events.event,  t_events.descript,  t_events.logdate
 10  /

Hi Tom,

When i try to use max(case...) with my subquery to extract the required date for a column, it throws following error:

Msg 130, Level 15, State 1, Line 2
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

Kindly suggest an alternative logic to use any aggregate function.

Hi Tom ,

Based the above posts i tried to create a view.
But its not working in my case...
Here my case is Rows to be trasposed in to Columns.

In my table data is like
Emp_ID Seq_No Field
101 9 ABC
101 10 DEF
101 11 XYZ

My requirement is
Emp_ID Field1(Seq 9) Field1(Seq 9) Field1( Seq 9)
101 ABC DEF XYZ


pls help on this

Sorry for uncleared question..

Here i tried to create a view based on the earlier posts.
Code is

CREATE OR REPLACE FORCE VIEW "SYSADM"."TEST_SPLIT_VW" ("EMP_ID","SPCH_SEQ","PAY_REG_CODE9", "PAY_REG_CODE10","REG_CODE11")
AS
SELECT BNS."EMP_ID",BNS."SPCH_SEQ",
(case when BNS.SPCH_SEQ=9 then BNS.PAY_REG_CODE end),
(case when BNS.SPCH_SEQ=10 then BNS.PAY_REG_CODE end),
(case when BNS.SPCH_SEQ=11 then BNS.PAY_REG_CODE end)
FROM BASE_TABLE BNS , CALENDER CAL
WHERE BNS.EFFDT = ( SELECT MAX (EFFDT) FROM BASE_TABLE BNS1 WHERE
BNS1.GEID_KEY = BNS.GEID_KEY AND BNS1.EFFDT <= CAL.CUTOFF_DT )
AND BNS.EFF_SEQ_NUM = ( SELECT MAX (EFF_SEQ_NUM) FROM BASE_TABLE BNS2
WHERE BNS2.CIC_GEID_KEY = BNS.GEID_KEY AND BNS2.EFFDT = BNS.EFFDT );


This is
In my table data is like
Emp_ID SPCH_SEQ PAY_REG_CODE
101 9 ABC
101 10 DEF
101 11 XYZ

My requirement is
Emp_ID Field1(Seq 9) Field1(Seq 9) Field1( Seq 9)
101 ABC DEF XYZ

I hope this will help.....

hi,

My requirement is that basically I will have to create a report.I will have to convert the rows into columns from the table while displaying in the report.The data in the table are as follows:

Item_No Date Short_Amt
I1 D1 S1
I1 D2 S2
I2 D3 S3
I2 D4 S4
I2 D5 S5
.
.
. Dn Sn


Now My output will be:

Item_No D1 D2 D3 D4 D5....Dn
I1 S1 S2
I2 S3 S4 S5....Sn

But the data is infinite.....

Can you please help???
Thanks in advance

Hi,

Thanks...............

But how can I get the dates as the heading and below that the respective short value?????

Item_No D1 D2 D3 D4 D5
I1 S1 S2
I2 S3 S4 S5

Hi,

Actually, I will have many date values so I can't hard code the dates.

By matrix report can you plz tell me how can I do that or any other solution other than DECODE???

Thanks.........

hi,Tom
i have a table like this
description vodafone mobinil land line
1-2010 3 3 2
2-2006 9 12 12
5-2007 4 8 9
i need to make it like
description 1-2010 2-2006 5-2007
vodafone 3 9 4
mobinil 3 12 8
land line 2 12 9
i need to do it dynamically to the future when dates increase.
thanks a lot

descrip   what         value
1-2010    vodaphone    3
1-2010    mobinil      3
1-2010    land line    2
....

I worked this out for practice and then couldn't resist posting it, because I found out by accident that UNPIVOT and PIVOT can be put in the same SELECT. 11GR2 makes this so easy!

> drop table T
/
table T dropped.

> create table T(DESCRIPTION, VODAFONE, MOBINIL, LAND_LINE) as
select '1-2010', 3, 3, 2 from DUAL union all
select '2-2006', 9, 12, 12 from DUAL union all
select '5-2007', 4, 8, 9 from DUAL
/
table T created.

-- QUERY BY HAND: UNPIVOT and PIVOT in the same SELECT!
> select * from T
UNPIVOT(VAL for operator in(VODAFONE, MOBINIL, LAND_LINE))
PIVOT(max(VAL) for DESCRIPTION in
('1-2010' as "1-2010",'2-2006' as "2-2006",'1-2010' as "5-2007" ))
/
OPERATOR  1-2010                 2-2006                 5-2007
--------- ---------------------- ---------------------- ----------
VODAFONE  3                      9                      3
LAND_LINE 2                      12                     2
MOBINIL   3                      12                     3

-- The only dynamic part is the list of pivot columns
> select
LISTAGG('''' || DESCRIPTION || ''' as "' || DESCRIPTION || '"', ',')
within group(order by DESCRIPTION) collist
from (select distinct description from t)
/
COLLIST
--------------------------------------------------------------
'1-2010' as "1-2010",'2-2006' as "2-2006",'5-2007' as "5-2007"

-- Query to generate the desired query with dynamic pivot columns
> select
'select * from T
UNPIVOT(VAL for operator in(VODAFONE, MOBINIL, LAND_LINE))
PIVOT(max(VAL) for DESCRIPTION in
(' ||
LISTAGG('''' || DESCRIPTION || ''' as "' || DESCRIPTION || '"', ',')
within group(order by DESCRIPTION)
||'))' sql_text from (select distinct DESCRIPTION from T)
/
SQL_TEXT
-----------------------------------------------------------------
select * from T
UNPIVOT(VAL for operator in(VODAFONE, MOBINIL, LAND_LINE))
PIVOT(max(VAL) for DESCRIPTION in
('1-2010' as "1-2010",'2-2006' as "2-2006",'5-2007' as "5-2007"))

-- Run the generated query
> select * from T
UNPIVOT(VAL for operator in(VODAFONE, MOBINIL, LAND_LINE))
PIVOT(max(VAL) for DESCRIPTION in
('1-2010' as "1-2010",'2-2006' as "2-2006",'5-2007' as "5-2007"))
/
OPERATOR  1-2010                 2-2006                 5-2007
--------- ---------------------- ---------------------- ---------
VODAFONE  3                      9                      4
LAND_LINE 2                      12                     9
MOBINIL   3                      12                     8

select * from T
UNPIVOT(VAL for operator in(VODAFONE, MOBINIL, LAND_LINE))
PIVOT(max(VAL) for DESCRIPTION in
('1-2010' as "1-2010",'2-2006' as "2-2006",'1-2010' as "5-2007" ))

From your forums i found this example is useful. if i have no of dates i cannot hard code. what is the bestway to modify above query.

OK, You just copied my manual query about one page up. You even left in the error I made on the third date :)

Now you ask how to change the query if the dates change. Don't you realize that is exactly what I showed one page up?
- the first manual query was just to show what the final query should look like.
- the second query gets the list of dates to pivot.
- the third query builds on the previous two: it dynamically generates the full query with the right list of dates.
- then I just run that dynamically generated query.

Exactly what Tom said, shown step by step. What do you think is missing?

I have a table like

subscr_no balance_id balance_type
681 101 1
681 102 2

There could be multiple balances(N) for one subscr_no

Now i want the output like

subscr_no balance_id1 balance_type1 balance_id2 blance_type2
681 101 1 102 2


is this possible with help of sql..your quick response will help me very much

Is there a way to turn the output of the following SQL from 3 values separated by a comma into 3 separate rows without using a function?

SELECT value
FROM v$parameter
WHERE name = 'control_files';

VALUE
-----------------------------------------------------------------------------------------------------------------------------------
C:\APP\ADMINISTRATOR\ORADATA\PROTECT\CONTROL01.CTL, C:\APP\ADMINISTRATOR\ORADATA\PROTECT\CONTROL02.CTL, C:\APP\ADMINISTRATOR\ORADAT
A\PROTECT\CONTROL03.CTL

Desired output:

C:\APP\ADMINISTRATOR\ORADATA\PROTECT\CONTROL01.CTL
C:\APP\ADMINISTRATOR\ORADATA\PROTECT\CONTROL02.CTL
C:\APP\ADMINISTRATOR\ORADATA\PROTECT\CONTROL03.CTL

ops$tkyte%ORA11GR2> show parameter control_files

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      /home/ora11gr2/app/ora11gr2/or
                                                 adata/ora11gr2/control01.ctl,
                                                 /home/ora11gr2/app/ora11gr2/fa
                                                 st_recovery_area/ora11gr2/cont
                                                 rol02.ctl
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> select rownum, column_value
  2    from (select ','||value||',' txt
  3            from v$parameter
  4           where name = 'control_files'),
  5         table( cast( multiset
  6        (select trim( substr (txt,
  7                              instr (txt, ',', 1, level  ) + 1,
  8                              instr (txt, ',', 1, level+1)
  9                                 - instr (txt, ',', 1, level) -1 ) )
 10           from dual
 11        connect by level <= length(txt)-length(replace(txt,',',''))-1
 12        ) as sys.odciVarchar2List ) )
 13  /

    ROWNUM COLUMN_VALUE
---------- ----------------------------------------------------------------------------------------------------
         1 /home/ora11gr2/app/ora11gr2/oradata/ora11gr2/control01.ctl
         2 /home/ora11gr2/app/ora11gr2/fast_recovery_area/ora11gr2/control02.ctl

Hi Tom,

I have a query where I have to turn rows into columns:

CREATE TABLE TABLE_1(
column_1 VARCHAR2(100),column_2 VARCHAR2(100),column_3 VARCHAR2(100),
column_4 VARCHAR2(100),column_5 VARCHAR2(100),column_6 VARCHAR2(100),
column_7 VARCHAR2(100),column_8 DATE,column_9 DATE
)

Insert into "table_1" (column_1,column_2,column_3,column_4,column_5,column_6,column_7,column_8,column_9) values ('VAL_1','VAL_2','00','I','07','col_val61',null,to_timestamp('01-JAN-00','DD-MON-RR HH.MI.SSXFF AM'),to_timestamp('31-DEC-99','DD-MON-RR HH.MI.SSXFF AM'));
Insert into "table_1" (column_1,column_2,column_3,column_4,column_5,column_6,column_7,column_8,column_9) values ('VAL_1','VAL_2','00','I','09','CHE',null,to_timestamp('01-JAN-00','DD-MON-RR HH.MI.SSXFF AM'),to_timestamp('31-DEC-99','DD-MON-RR HH.MI.SSXFF AM'));

Insert into "table_1" (column_1,column_2,column_3,column_4,column_5,column_6,column_7,column_8,column_9) values ('VAL_1','VAL_2','10','I','07','col_val61',null,to_timestamp('01-JAN-01','DD-MON-RR HH.MI.SSXFF AM'),to_timestamp('31-DEC-99','DD-MON-RR HH.MI.SSXFF AM'));
Insert into "table_1" (column_1,column_2,column_3,column_4,column_5,column_6,column_7,column_8,column_9) values ('VAL_1','VAL_2','10','P','06','col_val64',null,to_timestamp('01-JAN-00','DD-MON-RR HH.MI.SSXFF AM'),to_timestamp('31-DEC-99','DD-MON-RR HH.MI.SSXFF AM'));
Insert into "table_1" (column_1,column_2,column_3,column_4,column_5,column_6,column_7,column_8,column_9) values ('VAL_1','VAL_2','10','I','999','col_val62',null,to_timestamp('01-JAN-11','DD-MON-RR HH.MI.SSXFF AM'),to_timestamp('31-DEC-99','DD-MON-RR HH.MI.SSXFF AM'));

Insert into "table_1" (column_1,column_2,column_3,column_4,column_5,column_6,column_7,column_8,column_9) values ('VAL_1','VAL_2','20','E','71','11',null,to_timestamp('01-JAN-12','DD-MON-RR HH.MI.SSXFF AM'),to_timestamp('31-DEC-99','DD-MON-RR HH.MI.SSXFF AM'));
Insert into "table_1" (column_1,column_2,column_3,column_4,column_5,column_6,column_7,column_8,column_9) values ('VAL_1','VAL_2','20','E','06','CT1',null,to_timestamp('01-JAN-01','DD-MON-RR HH.MI.SSXFF AM'),to_timestamp('31-DEC-20','DD-MON-RR HH.MI.SSXFF AM'));
Insert into "table_1" (column_1,column_2,column_3,column_4,column_5,column_6,column_7,column_8,column_9) values ('VAL_1','VAL_2','20','I','07','col_val61',null,to_timestamp('01-JAN-00','DD-MON-RR HH.MI.SSXFF AM'),to_timestamp('31-DEC-99','DD-MON-RR HH.MI.SSXFF AM'));
Insert into "table_1" (column_1,column_2,column_3,column_4,column_5,column_6,column_7,column_8,column_9) values ('VAL_1','VAL_2','20','I','999','col_val62',null,to_timestamp('01-JAN-00','DD-MON-RR HH.MI.SSXFF AM'),to_timestamp('31-DEC-99','DD-MON-RR HH.MI.SSXFF AM'));

Now my desired output looks like:

r_column_1 r_column_2 r_column_3 r_column_4 r_column_5 r_column_6 r_column_7 r_column_8 r_column_9 r_column_10 r_column_11 r_column_12 r_column_13 r_column_14 r_column_15 r_column_16 r_column_17 r_column_18 r_column_19 r_column_20 r_column_21 r_column_22 r_column_23 r_column_24 r_column_25 r_column_26 r_column_27
VAL_1 VAL_2 00 I 07 col_val61 NULL 01-JAN-00 31-DEC-99 I 09 CHE NULL 01-JAN-00 31-DEC-99
VAL_1 VAL_2 10 I 07 col_val61 NULL 01-JAN-01 31-DEC-99 P 06 col_val64 NULL 01-JAN-00 31-DEC-99 I 999 col_val62 NULL 01-JAN-11 31-DEC-99
VAL_1 VAL_2 20 E 71 11 NULL 01-JAN-12 31-DEC-99 E 06 CT1 NULL 01-JAN-01 31-DEC-20 I 07 col_val61 NULL 01-JAN-11 31-DEC-99 I 999 col_val62 NULL 01-JAN-00 31-DEC-99

The logic here is:

I have to do the pivot on the basis of column_3 value.

1. Column_1, column_2 remain static
2. Rows are made on basis of Column_3 asa we had three different column_3 values here.
3. We can have upto 90 number of column_6. So which in turn grows my columns. As you can see for column_3 value = 00 i have two rows so my flattening goes till r_column_15 but for column_3 value=10, I have values till r_column_21



Please advise. I apologize for readability part.

ops$tkyte%ORA11GR2> select max(column_1), max(column_2), column_3,
  2         max( decode( rn, 1, column_4 ) ) c1_4,
  3         max( decode( rn, 1, column_5 ) ) c1_5,
  4         max( decode( rn, 1, column_6 ) ) c1_6,
  5         max( decode( rn, 1, column_7 ) ) c1_7,
  6         max( decode( rn, 1, column_8 ) ) c1_8,
  7         max( decode( rn, 1, column_9 ) ) c1_9,
  8         max( decode( rn, 2, column_4 ) ) c2_4,
  9         max( decode( rn, 2, column_5 ) ) c2_5,
 10         max( decode( rn, 2, column_6 ) ) c2_6,
 11         max( decode( rn, 2, column_7 ) ) c2_7,
 12         max( decode( rn, 2, column_8 ) ) c2_8,
 13         max( decode( rn, 2, column_9 ) ) c2_9,
 14         max( decode( rn, 3, column_4 ) ) c3_4,
 15         max( decode( rn, 3, column_5 ) ) c3_5,
 16         max( decode( rn, 3, column_6 ) ) c3_6,
 17         max( decode( rn, 3, column_7 ) ) c3_7,
 18         max( decode( rn, 3, column_8 ) ) c3_8,
 19         max( decode( rn, 3, column_9 ) ) c3_9
 20    from (
 21  select t.*, row_number() over (partition by column_3 order by rowid) rn
 22    from t
 23         )
 24   group by column_3
 25   order by column_3
 26  /

MAX(COLUMN MAX(COLUMN COLUMN_3   C1_4       C1_5       C1_6       C1_7       C1_8      C1_9      C2_4       C2_5       C2_6       C2_7       C2_8      C2_9      C3_4       C3_5       C3_6       C3_7       C3_8      C3_9
---------- ---------- ---------- ---------- ---------- ---------- ---------- --------- --------- ---------- ---------- ---------- ---------- --------- --------- ---------- ---------- ---------- ---------- --------- ---------
VAL_1      VAL_2      00         I          07         col_val61  *null*     01-JAN-00 31-DEC-99 I          09         CHE        *null*     01-JAN-00 31-DEC-99 *null*     *null*     *null*     *null*     *null*    *null*
VAL_1      VAL_2      10         I          07         col_val61  *null*     01-JAN-01 31-DEC-99 P          06         col_val64  *null*     01-JAN-00 31-DEC-99 I          999        col_val62  *null*     01-JAN-11 31-DEC-99
VAL_1      VAL_2      20         E          71         11         *null*     01-JAN-12 31-DEC-99 E          06         CT1        *null*     01-JAN-01 31-DEC-20 I          07         col_val61  *null*     01-JAN-00 31-DEC-99

Hi Tom,


In case we cannot make it dynamic, I would appreciate if you could suggest me with the hardcoded column names.

Thanks Tom,

It is working. Also, just wanted to know if we can name the columns based on the COLUMN6 value..

I have also taken help from one of your packages which deals with pivot dynamically:

SQL> variable x refcursor
SQL> set autoprint on
SQL> 
SQL> 
SQL> begin
  2  my_pkg.pivot
  3  ( p_max_cols_query => 'select max(count(*)) from t
  4  group by column_1, column_2, column_3',
  5  p_query => 'select column_1, column_2, column_3, column_5, column_6,
  6  row_number() over ( partition by column_1, column_2, column_3
  7  order by column_1, column_2, column_3 ) rn
  8  from t a',
  9  p_anchor => my_pkg.array('COLUMN_1', 'COLUMN_2', 'COLUMN_3'),
 10  p_pivot => my_pkg.array('COLUMN_6', 'COLUMN_5' ),
 11  p_cursor => :x );
 12  
 13  end;
 14  /

PL/SQL procedure successfully completed.


COLUMN_1   COLUMN_2   COLUMN_3   COLUMN_6_1 COLUMN_5_1 COLUMN_6_2 COLUMN_5_2 COLUMN_6_3 COLUMN_5_3  COLUMN_6_4 COLUMN_5_4         
----------------------------------------------------------------------------------------------------------------------------
VAL_1      VAL_2      00         121        07         101        07         37         09     

VAL_1      VAL_2      10         61         07         64         06         101        999   

VAL_1      VAL_2      20         37         71         101        06         61         07           62            999   

The problem I face here is it gives me the repetitions of COLUMN5 and COLUMN6 according to the count of rows.

As you can see that I had 4 different values when grouped by column_1, column_2 and column_3. The query broke the Column5 and column6 correctly as column5_1, column6_1 and so on...

Is it possible to have the rows look like:

COLUMN_1 COLUMN_2 COLUMN_3 COL6_121 COL_5_121 COL_6_101 COL_5_101 COL_6_37 COL_5_37 COL_6_62 COL_5_62 COL_6_61 COL_5_61 COL_6_64 COL_5_64
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
VAL_1 VAL_2 00 121 07 101 07 37 09
VAL_1 VAL_2 10 101 999 61 07 64 06
VAL_1 VAL_2 20 101 06 37 71 62 999 61 07


If this can be achieved then we can have a mapping of all column_6 values and the application can easily identify the columns.
I am trying t do it through PL SQL but am not able to put it in an elegant way.

Could you please help with one more time. In case this is not possible, I am still happy with the earlier suggested method.

Thanks for your kind help.

COLUMN_1 COLUMN_2 COLUMN_3 COL6_121 COL_5_121 COL_6_101 COL_5_101 COL_6_37 COL_5_37 COL_6_62 COL_5_62 COL_6_61 COL_5_61 COL_6_64 COL_5_64
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
VAL_1 VAL_2 00 121 07 101 07 37 09
VAL_1 VAL_2 10 101 999 61 07 64 06
VAL_1 VAL_2 20 101 06 37 71 62 999 61 07

Hi Tom,

I have been able to get the results in terms of values through dynamic PL SQL.


declare
v_final VARCHAR2(32000);
v_string VARCHAR2(10000);
v_sqls VARCHAR2(10000);


begin
v_sqls := 'SELECT column1, column2, column3,';
for ctr_editnum in (select distinct column6 from t)
loop
v_string := 'max( decode( column6, ' || ctr_editnum.column6 || ', column4 ) ) c_4_' || ctr_editnum.column6 || ',
max( decode( column6, ' || ctr_editnum.column6 || ', column5 ) ) c_5_' || ctr_editnum.column6 || ',
max( decode( column6, ' || ctr_editnum.column6 || ', column6 ) ) c_6_' || ctr_editnum.column6 || ',
max( decode( column6, ' || ctr_editnum.column6 || ', column7 ) ) c_7_' || ctr_editnum.column6 || ',
max( decode( column6, ' || ctr_editnum.column6 || ', column8 ) ) c_8_' || ctr_editnum.column6 || ',
max( decode( column6, ' || ctr_editnum.column6 || ', column9 ) ) c_9_' || ctr_editnum.column6 || ',
';
dbms_output.put_line(v_string);
v_final := v_final || v_string;
end loop;
v_final := trim(v_final);
v_sqls := v_sqls || substr(v_final,1,length(v_final)-2) || ' from t
group by column1, column2, column3';
dbms_output.put_line(v_sqls);
end;

I have only concern when I have more than one column_6 values for same column1, column2, column3 value. I am getting only one row since we are taking the MAX.

Can you please suggest how can we achive the same?


Insert into "table_1"
(column_1,column_2,column_3,column_4,column_5,column_6,column_7,column_8,column_9) values
('VAL_1','VAL_2','00','I','07','dup_values',null,to_timestamp('01-JAN-01','DD-MON-RR HH.MI.SSXFF
AM'),to_timestamp('31-OCT-99','DD-MON-RR HH.MI.SSXFF AM'));

I want both the values of COLUMN_6 in pivot table for the given column1, column2, column3 value
if it differs on COLUMN_5, COLUMN_7, COLUMN_8 or COLUMN_9

Thanks for the help.

For this issue, I would have wanted to have the group on COLUMN3 as it is happening now. For the repeating columns, COLUMN5 to COLUMN9 I would have preferred to do a concatenation so that I can break them when I use them.

e.g if my data was having multiple values of column6, I would listagg them with comma..

So if I break the comma all the first element of the string of different columns specify the corresponding value.

For data like this

c1   c2   c3   c4   c5   c6   c7   c8          c9
F1   S1   00   I    E    37        20-JAN-00   31-JAN-99
F1   S1   00   I    E    37   PP   20-DEC-01   31-JAN-99
F1   S1   00   I    E    05        20-JAN-00   31-JAN-99

c1   c2   c3   c4   c5   c6   c7   c8                    c9                    C10    C11     C12    C13     C14         C15
F1   S1   00   I    E    37   ,PP  20-JAN-00,20-DEC-01   31-JAN-99,31-JAN-99   I      E       05             20-JAN-00   31-JAN-99

As you see since my first row had C7 as blank and second had value as PP for the column6=37 I concatenate them as ,PP

which means when column6=37 and C8 = 20-Jan-00 my C7 was NULL.

Please suggest.

Apologies for the bad TAG habit. As you see I had to concatenate two dates which takes some space here so please next time I will take care. Thanks again.

decode can do this for thanks

Hi Tom,

Appreciate if you can help me to solve on this.

My table is like this

ACA_AC_NO ACA_RELATION_TXT ACA_PRIMARY_AC_F ACA_RELATION_CODE CUST_LAST_NAME
------------ ------ ----------- ---- --------------------
xxxxxxxxxxxx OWNER Y JFE Mr A
xxxxxxxxxxxx SON N JOE B
xxxxxxxxxxxx SON N JOE C
xxxxxxxxxxxx SPOUSE N JOE D

How can I display like this

Account Account 1 Relation 1 Account 2 Relation 2 Account 3 Relation 3
------- --------- ---------- --------- ---------- --------- ----------
xxxxxxxx Mr A OWNER B SON C SON

Hi Tom,
We have data in the temp table as below:
Key column name column value
1 C1 V1
1 C2 V2
1 C3 V3
2 C1 V11
2 C2 V12
2 C3 V13
We want to present data (table/view) as below:
Key C1 C2 C3
1 V1 V2 V3
2 V11 V12 V13
Is it possible to achieve this through query/oracle built-in function?
Regards,

Hello Tom,

I tried converting the rows into columns from your previous answers , i got the answers close enough and i cannot attain my desired output:
i need my output to be :

emp1
jenny
rita
jessy
rahul
vivek
bala

from the table :

col1 col2 col3
jenny rita jessy
rahul vivek bala

Thanks is advance ,

It would be greatful if you could me in solving this

ops$tkyte%ORA11GR2> with data
  2  as
  3  (
  4  select *
  5   from (select 'jenny' c1, 'rita' c2, 'jessy' c3 from dual union all
  6         select 'rahul' c1, 'vivek' c2, 'bala' c3 from dual)
  7  )
  8  select decode( r, 1, c1, 2, c2, 3, c3 )
  9    from data, (select 1 r from dual union all select 2 from dual union all select 3 from dual)
 10  /

DECOD
-----
jenny
rita
jessy
rahul
vivek
bala

6 rows selected.

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

5 rows selected.

SQL> -- generating sample data:
SQL> with data as (
  2  select 'jenny' c1, 'rita' c2, 'jessy' c3 from dual union all
  3  select 'rahul' c1, 'vivek' c2, 'bala' c3 from dual
  4  )
  5  --
  6  -- actual query:
  7  --
  8  select emp1
  9  from   data
 10  unpivot (emp1 for column_list in (c1, c2, c3));

EMP1
-----
jenny
rita
jessy
rahul
vivek
bala

6 rows selected.

Hii tom,
i have a similar problem the above solution is not working here
can you please help


I have a table Employee_details having columns

create table employee_details(
employee_no number,
info_type varchar2(25),
Passport_no number,
visa_no number,
working_time_directive number,
FA_Formation number)
/

having values like

insert into employee_details(employee_no,info_type,passport_no,visa_no,working_time_directive,fa_formation)
values(345618,'passport_details',234567,null,null,null)
/
insert into employee_details(employee_no,info_type,passport_no,visa_no,working_time_directive,fa_formation)
values(345618,'visa_details',null,3456,null,null)
/
insert into employee_details(employee_no,info_type,passport_no,visa_no,working_time_directive,fa_formation)
values(345618,'passport_details',45678,null,null,null)
/
insert into employee_details(employee_no,info_type,passport_no,visa_no,working_time_directive,fa_formation)
values(345618,'worktime_details',null,null,7689,null)
/



and my result is to display data as
i want o\p:

employee_no passport_no visa_no working_time_no fa_formation
345618 234567 3456 7689 4567
345618 45678 5678
345618 5674

the output i required is

for example for an employeee i am having 5 passport numbers 2 visa numbers 1 fa_formation
then
i should get in output as 5 rows ("i.e WHAT IS THE MAXIMUM NUMBER OF VALUES PRESENT") HERE passport has maximum values soo

in the first coloumn i should get all the 5 passport numbers
in the second coloumn i should get the 2 visa numbers
in such a way i should get 1 in fa_ formation COLOUMN
THE REMAINING SHOLUD BE NULL

ops$tkyte%ORA11GR2> variable empno number
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> exec :empno := 345618

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> with pp
  2  as
  3  (select passport_no, rownum r
  4     from employee_details
  5    where employee_no = :empno
  6      and info_type = 'passport_details' ),
  7  vn
  8  as
  9  (select visa_no, rownum r
 10     from employee_details
 11    where employee_no = :empno
 12      and info_type = 'visa_details' ),
 13  wt
 14  as
 15  (select working_time_directive, rownum r
 16     from employee_details
 17    where employee_no = :empno
 18      and info_type = 'worktime_details' ),
 19  pp_vn
 20  as
 21  (select coalesce( pp.r, vn.r ) r, pp.passport_no, vn.visa_no
 22    from pp full outer join vn on (pp.r = vn.r))
 23  select :empno, pp_vn.*, wt.working_time_directive
 24    from pp_vn full outer join wt on (pp_vn.r = wt.r)
 25  /

    :EMPNO          R PASSPORT_NO    VISA_NO WORKING_TIME_DIRECTIVE
---------- ---------- ----------- ---------- ----------------------
    345618          1      234567       3456                   7689
    345618          2       45678