How to "ASSIGN" a RecordID based on 1st character of a Field?

My data looks like this:

rowid      NewDetailData
 1      0BATCH-NEXTGEN-NEXTGENEMR-20160829CPM-651
 2      1P0007.63579.0000-1609168541-899972053-1230-CN25
 3      2P201608232016082399348-1-0-1
 4      51-401.9
 5      52-719.45
 6      71609168541
 7      1P0007.63512.0000-1093765679-100458751-1230-CN25
 8      2P201608262016082699347-1-0-1
 9      51-787.01
 10      71093765679
 11      1P0014.236945.0000-1174807291-2254912701-1230-GW23
 12      2P201608252016082599341-1-0-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-1609168541-899972053-1230-CN25
 3,1,2P201608232016082399348-1-0-1
 4,1,51-401.9
 5,1,52-719.45
 6,1,71609168541
 7,2,1P0007.63512.0000-1093765679-100458751-1230-CN25
 8,2,2P201608262016082699347-1-0-1
 9 ,2,51-787.01
 10,2,71093765679
 11,3,1P0014.236945.0000-1174807291-2254912701-1230-GW23
 12,3,2P201608252016082599341-1-0-1
 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!
LVL 17
MIKESoftware Solutions ConsultantAsked:
Who is Participating?
 
PortletPaulfreelancerCommented:
This is the closest I can get right now (time limited)
data
declare @YourTable table
    ([rowid] int, [NewDetailData] varchar(50))
;
    
INSERT INTO @YourTable
    ([rowid], [NewDetailData])
VALUES
    (1, '0BATCH-NEXTGEN-NEXTGENEMR-20160829CPM-651'),
    (2, '1P0007.63579.0000-1609168541-899972053-1230-CN25'),
    (3, '2P201608232016082399348-1-0-1'),
    (4, '51-401.9'),
    (5, '52-719.45'),
    (6, '71609168541'),
    (7, '1P0007.63512.0000-1093765679-100458751-1230-CN25'),
    (8, '2P201608262016082699347-1-0-1'),
    (9, '51-787.01'),
    (10, '71093765679'),
    (11, '1P0014.236945.0000-1174807291-2254912701-1230-GW23'),
    (12, '2P201608252016082599341-1-0-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')
;

Open in new window

query:
;with YT as (
SELECT
                rowid
              , NewDetailData
              , LEFT(NewDetailData, 1) AS left1
        FROM @yourtable
        WHERE LEFT(NewDetailData, 1) between '1' and '7'
       )
SELECT
          yt.rowid
        , ISNULL(oa1.nxt1, (select max(rowid) from yt)) AS RECORD_ID
        , yt.NewDetailData
FROM yt
OUTER APPLY (SELECT TOP (1)
                yt2.rowid AS nxt1
        FROM yt yt2
        WHERE yt2.rowid > yt.rowid
        AND yt2.left1 = '1'
        ORDER BY yt2.rowid) oa1
ORDER BY yt.rowid

Open in new window

result:
rowid RECORD_ID NewDetailData                                      
----- --------- -------------------------------------------------- 
2     7         1P0007.63579.0000-1609168541-899972053-1230-CN25   
3     7         2P201608232016082399348-1-0-1                      
4     7         51-401.9                                           
5     7         52-719.45                                          
6     7         71609168541                                        
7     11        1P0007.63512.0000-1093765679-100458751-1230-CN25   
8     11        2P201608262016082699347-1-0-1                      
9     11        51-787.01                                          
10    11        71093765679                                        
11    20        1P0014.236945.0000-1174807291-2254912701-1230-GW23 
12    20        2P201608252016082599341-1-0-1                      
13    20        51-402.10                                          
14    20        52-272.2                                           
15    20        53-250.00                                          
16    20        54-244.9                                           
17    20        55-414.01                                          
18    20        56-278.02                                          
19    20        57-V85.24                                          
20    20        71174807291  

Open in new window

0
 
PortletPaulfreelancerCommented:
I'm afraid your words don't seem to explain fully the second (new) column called  RowID_2

why id row_2 = 1 for these?
 2,1,1P0007.63579.0000-1609168541-899972053-1230-CN25
 3,1,2P201608232016082399348-1-0-1
 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-1093765679-100458751-1230-CN25
 8,2,2P201608262016082699347-1-0-1
 9 ,2,51-787.01
 10,2,71093765679

why id row_2 = 3 for these?
 11,3,1P0014.236945.0000-1174807291-2254912701-1230-GW23
 12,3,2P201608252016082599341-1-0-1
 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?
0
 
MIKESoftware Solutions ConsultantAuthor Commented:
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'...
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
MIKESoftware Solutions ConsultantAuthor Commented:
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....
0
 
MIKESoftware Solutions ConsultantAuthor Commented:
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
0
 
MIKESoftware Solutions ConsultantAuthor Commented:
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
0
 
PortletPaulfreelancerCommented:
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.
0
 
MIKESoftware Solutions ConsultantAuthor Commented:
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.