Columns to Rows

[neeraj]

Hi,

Here is my source data..
100 1 3800 11/05/2005 10:21:01
100 1 6000 11/06/2005 10:20:02 100 2 3800 11/05/2005 10:22:01
101 1 3800 11/05/2005 11:20:01
101 2 6000 11/05/2005 11:23:01


I want my target file to be populated as
100 1 11/05/2005 11:20:01 11/06/2005 10:20:02
( Max date for 3800) (max dt for 6000)
100 2 11/05/2005 10:22:01 11/05/2005 11:23:01


I have done this in server jobs, But I am not able to do it into parallel job.. Please suggest me how should i proccedd...

I tried the following steps using parallel jobs and got the following results

1. used OCIStage and use the following SQL
select NBR,SEQ, EVNT_CD, max (EVNT_EFCTV_DT)
from Kav group by SEQ,EVNT_CD,NBR

AND GOT tHE FOLLOWING RESULT:-

100 1 3800 11-MAY-05 11:20:01.000000 AM
100 1 6000 11-JUN-05 10:20:02.000000 AM
100 2 3800 11-MAY-05 10:22:01.000000 AM
100 2 6000 11-MAY-05 11:23:01.000000 AM

But I have no idea how to procceed to get the result like as follows:

100 1 11-MAY-05 11:20:01.000000 AM 11-JUN-05 10:20:02.000000 AM
100 2 11-MAY-05 10:22:01.000000 AM 11-MAY-05 11:23:01.000000 AM



Regards
Neeraj Mahajan

[Ultramundane]

Can you have your database engine do the work?

Code: Select all

SELECT K1.NBR,
            K1.SEQ, 
            ( SELECT MAX(EVNT_EFCTV_DT)
                  FROM Kav K2
                WHERE K1.NBR= K2.NBR
                    AND K1.SEQ = K2.SEQ
                    AND K2.EVNT_CD = 3800 ) AS "DT1",
            ( SELECT MAX(EVNT_EFCTV_DT)
                  FROM Kav K3
                WHERE K1.NBR= K3.NBR
                    AND K1.SEQ = K3.SEQ
                    AND K3.EVNT_CD = 6000 ) AS "DT2"
  FROM Kav K1
GROUP 
       BY K1.NBR,
            K1.SEQ

[kcbland]

How about some really simple SQL:

Code: Select all

SELECT NBR,
   SEQ, 
   max(case when EVNT_CD=3800 then EVNT_EFCTV_DT else NULL end) "EVNT_EFCTV_DT_3800", 
   max(case when EVNT_CD=6000 then EVNT_EFCTV_DT else NULL end) "EVNT_EFCTV_DT_6000"
from Kav group by SEQ,NBR 

[neeraj]

How about some really simple SQL:

Code: Select all

SELECT NBR,
   SEQ, 
   max(case when EVNT_CD=3800 then EVNT_EFCTV_DT else NULL end) "EVNT_EFCTV_DT_3800", 
   max(case when EVNT_CD=6000 then EVNT_EFCTV_DT else NULL end) "EVNT_EFCTV_DT_6000"
from Kav group by SEQ,NBR 

Hi


I tried this query.... and i got the following result

100 1 11-May-05 11:23:01 AM 11-Jun-05 10:20:02 AM
100 2 11-May-05 10:22:01 AM
101 1 11-May-05 11:20:01 AM
101 2 11-May-05 11:23:01 AM

But my expected results are:-
100 1 11-MAY-05 11:20:01.000000 AM 11-JUN-05 10:20:02.000000 AM
100 2 11-MAY-05 10:22:01.000000 AM 11-MAY-05 11:23:01.000000 AM


And the source data is:
100 1 3800 11/05/2005 10:21:01
100 1 6000 11/06/2005 10:20:02
100 2 3800 11/05/2005 10:22:01
101 1 3800 11/05/2005 11:20:01
101 2 6000 11/05/2005 11:23:01

Please do the needful..

Regards
NM

[neeraj]

Can you have your database engine do the work?

Code: Select all

SELECT K1.NBR,
            K1.SEQ, 
            ( SELECT MAX(EVNT_EFCTV_DT)
                  FROM Kav K2
                WHERE K1.NBR= K2.NBR
                    AND K1.SEQ = K2.SEQ
                    AND K2.EVNT_CD = 3800 ) AS "DT1",
            ( SELECT MAX(EVNT_EFCTV_DT)
                  FROM Kav K3
                WHERE K1.NBR= K3.NBR
                    AND K1.SEQ = K3.SEQ
                    AND K3.EVNT_CD = 6000 ) AS "DT2"
  FROM Kav K1
GROUP 
       BY K1.NBR,
            K1.SEQ

Hi tried this query.... and i got the following result

100 1 11-May-05 11:23:01 AM 11-Jun-05 10:20:02 AM
100 2 11-May-05 10:22:01 AM
101 1 11-May-05 11:20:01 AM
101 2 11-May-05 11:23:01 AM

But my expected results are:-
100 1 11-MAY-05 11:20:01.000000 AM 11-JUN-05 10:20:02.000000 AM
100 2 11-MAY-05 10:22:01.000000 AM 11-MAY-05 11:23:01.000000 AM


And the source data is:
100 1 3800 11/05/2005 10:21:01
100 1 6000 11/06/2005 10:20:02
100 2 3800 11/05/2005 10:22:01
101 1 3800 11/05/2005 11:20:01
101 2 6000 11/05/2005 11:23:01

Please do the needful..

Regards
NM

[Ultramundane]

I entered your data and I came up with this query. I hope someone can come up with a better answer for you. This query might not run well on your database and if you do run it, you may want your DBA (if not yourself) to validate indexes exist for this query.

Code: Select all

SELECT K1.NBR,
       K1.SEQ,
       (SELECT MAX(K2.EVNT_EFCTV_DT)
             FROM Kav K2
            WHERE K2.EVNT_CD = 3800
              AND K1.SEQ = K2.SEQ
              AND K2.NBR = ( SELECT MAX(K3.NBR)
                                FROM Kav K3
                               WHERE K3.EVNT_CD = K2.EVNT_CD
                                 AND K1.SEQ = K3.SEQ ) ),
       (SELECT MAX(K2.EVNT_EFCTV_DT)
             FROM Kav K2
            WHERE K2.EVNT_CD = 6000
              AND K1.SEQ = K2.SEQ
              AND K2.NBR = ( SELECT MAX(K3.NBR)
                                FROM Kav K3
                               WHERE K3.EVNT_CD = K2.EVNT_CD
                                 AND K1.SEQ = K3.SEQ ) )
  FROM Kav K1
 WHERE K1.NBR = ( SELECT MIN(NBR) FROM Kav )
 GROUP
    BY K1.NBR,
         K1.SEQ

[Bala R]

Neeraj,
The query that Ken has posted and results thereof should be correct. What is the second date in your output's second row -
100 2 11-MAY-05 10:22:01.000000 AM 11-MAY-05 11:23:01.000000 AM
Your source doesn't have a seq 2 for NBR 100 and EVNT_CD 6000 but your result seems to have it. Or otherwise what did you mean by max dt for 6000? Is it max across NBR and EVNT_CD alone?