sequencing through rows based on account number changes...

  • hi all,

    have a query, really taking lot of time to get through...

    i dont have any order in my source but i have to track the order based in whether tranid changed....(how to track whether some value changed...??)

    actually,from the source i may get more than one row for a tran id(kind of account number) but I need to load the destination based on one row for a tran id (in sequence)...if the sequence breaks and i get another row for same tran id i create it again..

    sample data:

    CREATE TABLE [dbo].[test](

    [tran_id] [nvarchar](14) NULL,

    [type] [nvarchar](3) NULL,

    [emp] [nvarchar](5) NULL,

    [action] [nvarchar](4) NULL

    )

    select * from test

    INSERT INTO test ( tran_id,tran_id,type,emp,action) VALUES ( '306', '306', 'PRB', '229', 'IHHE')

    INSERT INTO test ( tran_id,tran_id,type,emp,action) VALUES ( '306', '306', 'PRB', '229', 'CEMP')

    INSERT INTO test ( tran_id,tran_id,type,emp,action) VALUES ( '306', '306', 'PYN', '885', 'DHKP')

    INSERT INTO test ( tran_id,tran_id,type,emp,action) VALUES ( '306', '306', 'PYN', '885', 'INPH')

    INSERT INTO test ( tran_id,tran_id,type,emp,action) VALUES ( '314', '314', 'PRB', '881', 'CEMP')

    INSERT INTO test ( tran_id,tran_id,type,emp,action) VALUES ( '314', '314', 'PYA', '881', 'INPH')

    INSERT INTO test ( tran_id,tran_id,type,emp,action) VALUES ( '316', '316', 'PYN', '338', 'CEMP')

    INSERT INTO test ( tran_id,tran_id,type,emp,action) VALUES ( '320', '320', 'MLA', '877', 'CEMP')

    INSERT INTO test ( tran_id,tran_id,type,emp,action) VALUES ( '324', '324', 'PYA', '429', 'DHKP')

    INSERT INTO test ( tran_id,tran_id,type,emp,action) VALUES ( '334', '334', 'PYA', '906', 'DHKP')

    INSERT INTO test ( tran_id,tran_id,type,emp,action) VALUES ( '334', '334', 'CBK', '906', 'CEMP')

    INSERT INTO test ( tran_id,tran_id,type,emp,action) VALUES ( '346', '346', 'SHD', '630', 'GECD')

    INSERT INTO test ( tran_id,tran_id,type,emp,action) VALUES ( '360', '360', 'PYN', '669', 'CANC')

    INSERT INTO test ( tran_id,tran_id,type,emp,action) VALUES ( '360', '360', 'RAE', '669', 'GMVD')

    INSERT INTO test ( tran_id,tran_id,type,emp,action) VALUES ( '362', '362', 'PYA', '187', 'DHKP')

    INSERT INTO test ( tran_id,tran_id,type,emp,action) VALUES ( '362', '362', 'PYA', '187', 'INPH')

    INSERT INTO test ( tran_id,tran_id,type,emp,action) VALUES ( '362', '362', 'PYA', '187', 'DHKP')

    INSERT INTO test ( tran_id,tran_id,type,emp,action) VALUES ( '362', '362', 'PYA', '187', 'INPH')

    INSERT INTO test ( tran_id,tran_id,type,emp,action) VALUES ( '306', '306', 'PYN', '707', 'DHKP')

    INSERT INTO test ( tran_id,tran_id,type,emp,action) VALUES ( '306', '306', 'PYN', '707', 'INPH')

    the output of this data should be somemthing like this:

    tran id empno action1 action 2 action3 action 4

    306 229 IHHE CEMP DHKP INPH

    314 881 CEMP INPH

    316 338 CEMP

    320 877 CEMP

    324 429 DHKP DHKP CEMP

    ..

    ..

    306 707 DHKP INPH

    actually i have to create one row for every incoming tranid and take the first empno for that tranid and put it in the row then I have to go thourgh all the rows and until the tranid changes i have to get the action and put them in separate action columns making 4 rows for trainid 306 to only one row like this:

    source shows:

    1 306 PRB 229 IHHE

    2 306 PRB 229 CEMP

    3 306 PYN 885 DHKP

    4 306 PYN 885 INPH

    I get:

    tran id empno action1 action 2 action3 action 4

    306 229 IHHE CEMP DHKP INPH

    thanks

    [font="Comic Sans MS"]
    ---------------------------------------------------

    Thanks [/font]

  • Triple post.

    So far, most replies here.



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply