Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

sequencing through rows based on account number changes... Expand / Collapse
Author
Message
Posted Thursday, July 01, 2010 1:28 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, December 10, 2013 11:37 AM
Points: 328, Visits: 843
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
Post #946435
Posted Thursday, July 01, 2010 4:06 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:25 PM
Points: 6,932, Visits: 12,665
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
Post #946520
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse