OraFAQ Forum: SQL & PL/SQL » Convert tabular format to column format

Home » SQL & PL/SQL » SQL & PL/SQL » Convert tabular format to column format

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Convert tabular format to column format [message #7290]

Mon, 02 June 2003 13:21

Heidi
Messages: 37
Registered: February 2000

Member

Hi... I have a query that produces the results i need, only in a long tabular list like this:

DESOWNER EVENTTYPE FACILITYCODE COUNT
OCL gateIn AUADLMENA 248
OCL gateIn AUBNEASDM 110
OCL gateOut USELPGA1A 381
OCL gateOut AUADLMENA 2
OCL estimate NZAKLUSGA 228
OCL estimate AUADLMENA 269

What I really want is for it to be formatted to display already in the columns so I don't have to put into excel every time. Such as this:

GATEIN GATEOUT ESTIMATE
AUADLMENA 248 2 269
AUBNEASDM 110 0 0

Is there a way to change this query to do what I want? Any help is greatly appreciated!

select ev.designatedOwner, ev.eventtype, s.facilitycode,
count (*)
from
facilitysegment s,
equipment e,
(select facilitysegmentid, designatedOwner, eventtype, equipmentnumber, transactionNumber,
timestamp, rank() OVER (partition by facilitySegmentid, designatedOwner, eventtype,
equipmentnumber, transactionNumber order by timestamp ) as event_rank from equipmentevent )ev
where ev.timestamp >= '1-May-2003' and ev.timestamp < '1-jun-2003'
and ev.designatedOwner = 'OCL'
/* and s.facilitycode= 'ARBUEICBA' */
/* and ev.eventtype = 'estimate' */
/* and ee.equipmentnumber = 'TRIU458507' */
and ev.facilitysegmentid = s.facilitysegmentid
and e.equipmentnumber= s.equipmentnumber
and ev.event_rank = 1
and eventtype in ('gateIn', 'gateOut', 'estimate')
group by designatedOwner, eventtype, facilitycode

Report message to a moderator

Re: Convert tabular format to column format [message #7291 is a reply to message #7290]

Mon, 02 June 2003 14:09

Art Metzer
Messages: 2480
Registered: December 2002

Senior Member

Hi, Heidi.

Try

SELECT   s.facilitycode
,        SUM(DECODE(ev.eventtype,'gateIn',1,0))      gate_in
,        SUM(DECODE(ev.eventtype,'gateOut',1,0))     gate_out
,        SUM(DECODE(ev.eventtype,'estimate',1,0))    estimate
FROM     facilitysegment      s
,        equipment            e
,       (SELECT facilitysegmentid
         ,      designatedowner
         ,      eventtype
         ,      equipmentnumber
         ,      transactionnumber
         ,      timestamp
         ,      RANK() OVER (PARTITION BY facilitysegmentid
                             ,            designatedowner
                             ,            eventtype
                             ,            equipmentnumber
                             ,            transactionnumber
                             ORDER BY     timestamp) AS event_rank
         FROM equipmentevent) ev
WHERE    ev.timestamp >= TO_DATE('01-MAY-2003'
                         ,       'DD-MON-YYYY')
AND      ev.timestamp < TO_DATE('01-JUN-2003'
                        ,       'DD-MON-YYYY')
AND      ev.designatedowner   = 'OCL' 
AND      ev.facilitysegmentid = s.facilitysegmentid
AND      e.equipmentnumber    = s.equipmentnumber
AND      ev.event_rank        = 1
AND      ev.eventtype        IN ('gateIn'
                             ,   'gateOut'
                             ,   'estimate')
GROUP BY s.facilitycode
ORDER BY s.facilitycode

Good luck,

A

Report message to a moderator

Re: Convert tabular format to column format [message #7299 is a reply to message #7291]

Tue, 03 June 2003 10:17

Heidi
Messages: 37
Registered: February 2000

Member

Thank you Art, that's perfect!!!

Report message to a moderator

Previous Topic:	Nondefault NULL in query
Next Topic:	PUBBLD.SQL required while connecting

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 16:41:06 CDT 2024