The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.
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
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
Who is Participating?
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with Premium.
Start your 7-day free trial.
I wear a lot of hats...
"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.
Experts Exchange Solution brought to you by
Your issues matter to us.
Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.
Start your 7-day free trial
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...
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...
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,c
P0007.63579.0000,160916854
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,c
P0007.63579.0000,160916854
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
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.
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.
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008
From novice to tech pro — start learning today.
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with Premium.
Start your 7-day free trial.
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