How to "ASSIGN" a RecordID based on 1st character of a Field?
My data looks like this:
rowid NewDetailData
1 0BATCH-NEXTGEN-NEXTGENEMR-
2 1P0007.63579.0000-16091685
3 2P201608232016082399348-1-
4 51-401.9
5 52-719.45
6 71609168541
7 1P0007.63512.0000-10937656
8 2P201608262016082699347-1-
9 51-787.01
10 71093765679
11 1P0014.236945.0000-1174807
12 2P201608252016082599341-1-
13 51-402.10
14 52-272.2
15 53-250.00
16 54-244.9
17 55-414.01
18 56-278.02
19 57-V85.24
20 71174807291
A single RECORD consists of having the 1st Character of the DetailData field between 1-7.
For example RowID 2-6 are for a SINGLE RECORD, RowID 7-10 are for a SINGLE RECORD, etc...etc..
I need a SQL SELECT Script that will "assign" a unique RowID_2 to all of the ROWS (1-7) for each record. I need something like this:
rowid , RowID_2, NewDetailData
2,1,1P0007.63579.0000-1609
3,1,2P20160823201608239934
4,1,51-401.9
5,1,52-719.45
6,1,71609168541
7,2,1P0007.63512.0000-1093
8,2,2P20160826201608269934
9 ,2,51-787.01
10,2,71093765679
11,3,1P0014.236945.0000-11
12,3,2P2016082520160825993
13,3,51-402.10
14,3,52-272.2
15,3,53-250.00
16,3,54-244.9
17,3,55-414.01
18,3,56-278.02
19,3,57-V85.24
20,3,71174807291
Thanks!
rowid NewDetailData
1 0BATCH-NEXTGEN-NEXTGENEMR-
2 1P0007.63579.0000-16091685
3 2P201608232016082399348-1-
4 51-401.9
5 52-719.45
6 71609168541
7 1P0007.63512.0000-10937656
8 2P201608262016082699347-1-
9 51-787.01
10 71093765679
11 1P0014.236945.0000-1174807
12 2P201608252016082599341-1-
13 51-402.10
14 52-272.2
15 53-250.00
16 54-244.9
17 55-414.01
18 56-278.02
19 57-V85.24
20 71174807291
A single RECORD consists of having the 1st Character of the DetailData field between 1-7.
For example RowID 2-6 are for a SINGLE RECORD, RowID 7-10 are for a SINGLE RECORD, etc...etc..
I need a SQL SELECT Script that will "assign" a unique RowID_2 to all of the ROWS (1-7) for each record. I need something like this:
rowid , RowID_2, NewDetailData
2,1,1P0007.63579.0000-1609
3,1,2P20160823201608239934
4,1,51-401.9
5,1,52-719.45
6,1,71609168541
7,2,1P0007.63512.0000-1093
8,2,2P20160826201608269934
9 ,2,51-787.01
10,2,71093765679
11,3,1P0014.236945.0000-11
12,3,2P2016082520160825993
13,3,51-402.10
14,3,52-272.2
15,3,53-250.00
16,3,54-244.9
17,3,55-414.01
18,3,56-278.02
19,3,57-V85.24
20,3,71174807291
Thanks!
ASKER
The assignment of RowID_2 the numbers 1,2,3 was purely done based on SEQUENCE. This RowID_2 will simply increment by one throughout the entire LIST of RECORDS. I'm attempting to GROUP all records together by using the RowID_2 and assigning it a unique ID field.
RowID starts with 2 because the first row of data, in the DetailData field started with a '0', not '1-7'. I don't need any rows returned that have a DetailData start character of '0', just ignore it. Incidentally, this is the FILE HEADER....and there is a file FOOTER...which I think starts with '9'.
So I don't need rows where DetailData starts with a '0' or a '9'...
RowID starts with 2 because the first row of data, in the DetailData field started with a '0', not '1-7'. I don't need any rows returned that have a DetailData start character of '0', just ignore it. Incidentally, this is the FILE HEADER....and there is a file FOOTER...which I think starts with '9'.
So I don't need rows where DetailData starts with a '0' or a '9'...
ASKER
Additionally, I guess a BETTER name for the column "RowID_2" should be.... "RECORD_ID".
Since this column really identifies the RECORD which spans multiple ROWS of data... 1-7....
FYI,...once I can assign this RECORD_ID, then I'll use it to further build my code to concatenate rows 1-7 INTO A SINGLE ROW OF DATA..for each RECORD.... which is my ultimate goal....
Since this column really identifies the RECORD which spans multiple ROWS of data... 1-7....
FYI,...once I can assign this RECORD_ID, then I'll use it to further build my code to concatenate rows 1-7 INTO A SINGLE ROW OF DATA..for each RECORD.... which is my ultimate goal....
ASKER
Here is my ultimate goal..... for which in order to achieve... I need the RECORD_ID assigned...
https://www.experts-exchange.com/questions/28967659/How-to-CONCANTENATE-ROWS-of-data-into-a-flat-single-ROW.html
https://www.experts-exchange.com/questions/28967659/How-to-CONCANTENATE-ROWS-of-data-into-a-flat-single-ROW.html
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
VERY NICE WORK! This is exactly what I was needing in order to move on to my next task in building my code....many..many thanks.
Also, please see this link for the next step...I will award points FAST for a solution...thx
Link to next step / Related question:
https://www.experts-exchange.com/questions/28967659/How-to-CONCANTENATE-ROWS-of-data-into-a-flat-single-ROW.html
Also, please see this link for the next step...I will award points FAST for a solution...thx
Link to next step / Related question:
https://www.experts-exchange.com/questions/28967659/How-to-CONCANTENATE-ROWS-of-data-into-a-flat-single-ROW.html
wasn't able to get to your other question as it's deleted now, so I presume you found a way to produce a comma separated list.
ASKER
Here is the latest question I have.
https://www.experts-exchange.com/questions/28967694/How-to-PARSE-a-text-field-that-is-delimited-by-'-'-character.html
https://www.experts-exchange.com/questions/28967694/How-to-PARSE-a-text-field-that-is-delimited-by-'-'-character.html
why id row_2 = 1 for these?
2,1,1P0007.63579.0000-1609
3,1,2P20160823201608239934
4,1,51-401.9
5,1,52-719.45
6,1,71609168541
why id row_2 = 2 for these?
7,2,1P0007.63512.0000-1093
8,2,2P20160826201608269934
9 ,2,51-787.01
10,2,71093765679
why id row_2 = 3 for these?
11,3,1P0014.236945.0000-11
12,3,2P2016082520160825993
13,3,51-402.10
14,3,52-272.2
15,3,53-250.00
16,3,54-244.9
17,3,55-414.01
18,3,56-278.02
19,3,57-V85.24
20,3,71174807291
and why does rowid start at 2 in the second list?