Query Help -- 2

[james777]

I need to collapse rows into one appened columns
Example: Input
col1 col2 col3 col4

1 A YY Oracle
1 A YY Siebel
1 A YY Sysbase

OutPut: Should be in single row as

1 A YY Oracle,Siebel,Sysbase..

There might be 1.. n Col4 values Unknown..

Thanks

 

[acct98]

Its me again. Everything works fine. However, it taking too long to execute the query on a table containing 400K records. The query has been running for 3+ hours. Any ideas on how to speed it up? I know that that hard to do because no one is here but I just looking for a few ideas.

 

[SantaMufasa]

Acct,

For speed, you must have an index on TEST1(SSN)...Have you? Without such an index, a full table scan occurs with each function call.

Let us know your findings.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 01:49 (19Jan05) UTC (aka "GMT" and "Zulu"),
@ 18:49 (18Jan05) Mountain Time

Click here to Donate to Tsunami Relief. 100% of your contributions here go to the victims...0% to administration.
They were "The First-Responder" to the disaster, with relief deliveries arriving before Red Cross and U.S. aid.

 

[notadba]

Hi,
I am trying to work out the logic to create a reporting data structure from a cumbersome transactional table;

Table structure (simplified – has about 20 columns removed for simplicity)
ID Date Category Value

For each ID on a given date there will always be 8 rows of data, each with a different category, and associated value for that category. There are only 8 possible category values, and each employee will always have all 8 for each date. Currently about 3.3 million rows of data in the table.

Needless to say, this table sucks to report from as it requires a number of loops to get all 8 categories for a given date.

How can I pivot this table out with sql to produce at able with the following structure

example of data and that I have
ID Date Category Value
1000 01/01/2005 SRV A
1000 01/01/2005 SRT B
1000 01/01/2005 FML C
1000 01/01/2005 FNC D
1000 01/01/2005 SFN E
1000 01/01/2005 SKG F
1000 01/01/2005 RKE G
1000 01/01/2005 WRK H
1000 12/12/2002 SRV A1
1000 12/12/2002 SRT B1
1000 12/12/2002 FML C1
1000 12/12/2002 FNC D1
1000 12/12/2002 SFN E1
1000 12/12/2002 SKG F1
1000 12/12/2002 RKE G1
1000 12/12/2002 WRK H1

When pivoted I want (date format not an issue)
ID date SRV SRT FML FNC SFN SKG RKE WRK
1000 01/01/2005 A B C D E F G H
1000 12/12/2002 A1 B1 C1 D1 E1 F1 G1 H1

Thanks in advance

 

[SantaMufasa]

NotADBA,

First, you and your question are worthy of a thread of your own.

Second, there are many alternatives to resolve your need...here is one:

col x format a3
col srv like x
col srt like x
col fml like x
col fnc like x
col sfn like x
col skg like x
col wrk like x
select id
    , dt
    , max(decode(category,’SRV’,val)) SRV
    , max(decode(category,’SRT’,val)) SRT
    , max(decode(category,’FML’,val)) FML
    , max(decode(category,’FNC’,val)) FNC
    , max(decode(category,’SFN’,val)) SFN
    , max(decode(category,’SKG’,val)) SKG
    , max(decode(category,’WRK’,val)) WRK
from notadba
group by id, dt
/

        ID DT         SRV SRT FML FNC SFN SKG WRK
---------- ---------- --- --- --- --- --- --- ---
      1000 01/01/2005 A   B   C   D   E   F   H
      1000 12/12/2002 A1  B1  C1  D1  E1  F1  H1

2 rows selected.

Let us know your thoughts.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.