sequencing through rows based on account number changes...

• sql_learner29

  SSCrazy Eights

  Points: 9107

  More actions

  July 1, 2010 at 1:28 pm

  #222787

  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]

• LutzM

  SSC Guru

  Points: 107049

  More actions

  July 1, 2010 at 4:06 pm

  #1187944

  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]