Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


sequencing through rows based on account number changes...


sequencing through rows based on account number changes...

Author
Message
NoraG
NoraG
Mr or Mrs. 500
Mr or Mrs. 500 (596 reputation)Mr or Mrs. 500 (596 reputation)Mr or Mrs. 500 (596 reputation)Mr or Mrs. 500 (596 reputation)Mr or Mrs. 500 (596 reputation)Mr or Mrs. 500 (596 reputation)Mr or Mrs. 500 (596 reputation)Mr or Mrs. 500 (596 reputation)

Group: General Forum Members
Points: 596 Visits: 916
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


---------------------------------------------------

Thanks

LutzM
LutzM
SSCrazy Eights
SSCrazy Eights (9.1K reputation)SSCrazy Eights (9.1K reputation)SSCrazy Eights (9.1K reputation)SSCrazy Eights (9.1K reputation)SSCrazy Eights (9.1K reputation)SSCrazy Eights (9.1K reputation)SSCrazy Eights (9.1K reputation)SSCrazy Eights (9.1K reputation)

Group: General Forum Members
Points: 9119 Visits: 13559
Triple post.
So far, most replies here.



Lutz
A pessimist is an optimist with experience.

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


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search