Solved

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

Posted on 2016-08-31
10
69 Views
Last Modified: 2016-09-04
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
0
Comment
Question by:MIKE
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
10 Comments
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 250 total points
ID: 41778311
Use a splitter function.  dbo.DelimitedSplit8K is excellent, you can Google for its source code.

SELECT mt.RowID,
    MAX(CASE WHEN ds.ItemNumber = 1 THEN ds.Item END) AS Column1,
    MAX(CASE WHEN ds.ItemNumber = 2 THEN ds.Item END) AS Column2,
    MAX(CASE WHEN ds.ItemNumber = 3 THEN ds.Item END) AS Column3,
    MAX(CASE WHEN ds.ItemNumber = 4 THEN ds.Item END) AS Column4 --,...
FROM dbo.main_table mt
CROSS APPLY dbo.DelimitedSplit8K (mt.DetailData, CHAR(9)) ds
GROUP BY mt.RowID
0
 
LVL 17

Author Comment

by:MIKE
ID: 41778809
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...
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 41778829
Yeah, but a tab is a single character delimiter.  CHAR(9) iirc.
0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 17

Author Comment

by:MIKE
ID: 41778892
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
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 41778907
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.
0
 
LVL 17

Author Comment

by:MIKE
ID: 41778991
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
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 41779873
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.
0
 
LVL 1

Assisted Solution

by:Brad Featherstone
Brad Featherstone earned 250 total points
ID: 41781821
This sorta look like a debug/telemetry feed from a device. A proposed solution is attached.

Nauseating Detail
You have an ordered set of input records that are individually typed. The input set is structured, a single unit starts with a Type 1 record. It looks like Type 1, 2, and 7 are always present. The unit may contain one or more type 5 records.

I ignored the record ids that you provided because it makes for a more generic solution.

So the problems are:
1) Create formal record types
2) Formally associate the records of a unit
3) Create a single record from the unit.

First create the dummy data set/feed.
Then, create an internal table to hold the data and associated row and unit identities within the procedure.  I used a table variable.

Problem 1 - Create formal record types
Easy peasy - Strip the first byte off of data and call it record type.  Also individually id each record while jamming it into internal table.

Problem 2 - Formally associate the records of a unit
Find each Type 1 record id and give it a unique rnum identity (ROW_NUMBER() function)
Self join the result to itself by rnum of current record + 1 = next rnum yielding a pair of preceding and following Type 1 record pairs.  Jigger the results to account for NULL in last pair and use of inclusive BETWEEN logic in update. Once the unit ids (GroupId) are assigned, the row ids are no longer used.

3) Create a single record from the unit
As noted in the code, You must create a table-valued function string parser.
This is pretty straight forward.  There are two kinds of records in a unit: Types 1, 2, and 7 are parsed into individual named fields via a table-valued function.  Type 5 records look like attribute-value pairs already.
Simply PIVOT the type 5 records to get a single record per unit (Detail).
Parse, UNION, and then PIVOT the Type 1, 2, and 7 to get a single record per unit (Header).  I did this in two logical steps.
Join Header and Detail to yield a single row per unit.
0
 
LVL 17

Author Comment

by:MIKE
ID: 41783974
many thanks!!
0
 
LVL 17

Author Closing Comment

by:MIKE
ID: 41784007
many thanks!!
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question