Link to home
Create AccountLog in
Avatar of Marcus Aurelius
Marcus AureliusFlag for United States of America

asked on

SQL Server - how to parse data from a single column into separate columns TAB Delimited

My data table looks like this:

RowID      DetailData
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

The Detailed Data column needs to be separated into separate COLUMNS of data based on the TAB spacing.

Each Full Record contains Rows that start with "1,2,5 and 7". I believe there can be 1 or many rows for each "1,2,5,7" but it appears that most multiple rows show up in the "5" type.

I need help in creating a sql script that will step through this table and separate the DetailedData field into separate fields.

Thanks
ASKER CERTIFIED SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of Marcus Aurelius

ASKER

Thanks for the input.

Can this be used with a "tab delimited" source field?

The documentation states that it is specifically coded for a "Single Character Delimiter", change at your own risk...
Yeah, but a tab is a single character delimiter.  CHAR(9) iirc.
What I'm really looking for as a result is this:

For example, for ROWID 1-6, I need to take that "DetailData" field for each ROWID and parse it out into a single FLAT ROW.

This should be the result for the RowIDs 1-6 should be:

col1,col2,col3,col4,col5,col6,col7,col8,col9,col10,col11,col12
P0007.63579.0000,1609168541,899972053,1230,CN25,P201608232016082399348,1,0,1,401.9,719.45,1609168541
I can't provide exact code for that since I don't have ready-to-use data.  But I think a combination of ROW_NUMBER() and the split could be made to work.
Here is the data sample:

rowid,detaildata
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
I don't have time to convert that splat of data into create table and insert statements.  that takes more time -- and is far more boring -- than writing the final query.
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
many thanks!!
many thanks!!