Poor Data Model

Question

Poor Data Model

AndrewSQLDBA

SSC-Insane

Points: 21827
More actions
October 7, 2011 at 10:33 am

#246097

Hello Everyone
Happy Friday
I have a request to perform almost a miracle. Someone that did not know how to design a database, allowed this. The data came from a very old mainframe system.
I have a string of data stored in a single column. But wait, it gets even better. The string is not always the same, and there are actually multiple records in each column. Meaning there could be one, or many.
This is what the data looks like in the column:
1|I9||NECK SHOULDER BACK PAIN||A|| 2|I9|V571|PHYSICAL THERAPY NEC||P|""|470 3|I9|7242|LUMBAGO||S1|| 4|I9|71941|JOINT PAIN-SHLDER||S2|| 5|I9|71942|JOINT PAIN-UP/ARM||S3|| 6|I9|7231|CERVICALGIA||S4|| 7|I9|7840|HEADACHE||S5|| 8|I9|9070|LT EFF INTRACRANIAL INJ||S6||
Pipe delimited string of multiple records. The numeric value at the beginning indicates there are 8 records in this one column.
I would like to be able to make the data look like this:
1|I9||NECK SHOULDER BACK PAIN||A||
2|I9|V571|PHYSICAL THERAPY NEC||P|""|470
3|I9|7242|LUMBAGO||S1||
4|I9|71941|JOINT PAIN-SHLDER||S2||
5|I9|71942|JOINT PAIN-UP/ARM||S3||
6|I9|7231|CERVICALGIA||S4||
7|I9|7840|HEADACHE||S5||
8|I9|9070|LT EFF INTRACRANIAL INJ||S6||
And then Insert each row into a table correctly. One nice thing, is there is always an exact number of columns, 8. Some may be blank
I am trying to get this for work, but I know there must be a better way that I have, because what I have tried, is not working.
I am hoping that someone can suggest a nice, clean and some what efficient means of inserting this data into a well defined table, with 8 columns.
Thank You in advance for your help.
Andrew SQLDBA

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

sturner SSC-Insane Points: 22411 More actions · Answer 1

If it were me I'd write a little C# program to process and load the pipe delimited rows and then insert into another table. You could split each row around the "I9", this is easier to do in C# (or, perish the though, VB.NET) than TSQL I think. This program would take about 5 minutes to write and should run very fast. You need to create the table that will be loaded when it runs.

The probability of survival is inversely proportional to the angle of arrival.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

Should be doable, but not until I get home.

Could you put together a test suite, table(s), sample data, expected results?

Should be able to do this in SQL, looks like something I had to do at a previous employer.

AndrewSQLDBA SSC-Insane Points: 21827 More actions · Answer 3

Yes, that I could. I can use C# in a SSIS package. My problem is that I am not sure where to begin with it.

I am able to replace the late Pipe with a different character, such as a comma. I can now have a distinct record delimiter. I am just not so good with string manipulations.

Any advice?

Thanks in advance

Andrew SQLDBA

AndrewSQLDBA SSC-Insane Points: 21827 More actions · Answer 4

Thanks Lynn

I will do just that for you. I will post it in just a bit

I am able to replace the last pipe, with a different character, I can now have a distinct delimiter for a record.

Thanks in advance

Andrew SQLDBA

sturner SSC-Insane Points: 22411 More actions · Answer 5

AndrewSQLDBA (10/7/2011)
Yes, that I could. I can use C# in a SSIS package. My problem is that I am not sure where to begin with it.
I am able to replace the late Pipe with a different character, such as a comma. I can now have a distinct record delimiter. I am just not so good with string manipulations.
Any advice?
Thanks in advance
Andrew SQLDBA

Here is a hint. Read each row then split the string on the pipe delimeter. You then have a string array containing the values for each column. Loop through the array processing each value (converting it where necessary) assign to bound parameters of an insert statement. When you see the "I9" you know you are at the second column of the next row so insert the previous row and go to the next.

In essence, because of the variable nature of these columns you'll have a nested loop. This can be done in TSQL also but the code to do it efficiently is not a intuuitive if you've never done it before. This sort of thing is just easier to do in C#.

The probability of survival is inversely proportional to the angle of arrival.

Sean Lange SSC Guru Points: 286590 More actions · Answer 6

This should be totally doable in tsql. A combination of Jeff Moden's splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/[/url] and maybe a for xml should just about get it there.

I don't have much time to play with it today but it splits easily.

declare @Mess varchar(8000) = '1|I9||NECK SHOULDER BACK PAIN||A|| 2|I9|V571|PHYSICAL THERAPY NEC||P|""|470 3|I9|7242|LUMBAGO||S1|| 4|I9|71941|JOINT PAIN-SHLDER||S2|| 5|I9|71942|JOINT PAIN-UP/ARM||S3|| 6|I9|7231|CERVICALGIA||S4|| 7|I9|7840|HEADACHE||S5|| 8|I9|9070|LT EFF INTRACRANIAL INJ||S6||'

select *

from DelimitedSplit8K(@Mess, '|')

From here just need to reassemble into columns.

--EDIT just a bit of modulus math and a pivot????

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

Sean Lange (10/7/2011)
This should be totally doable in tsql. A combination of Jeff Moden's splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/[/url] and maybe a for xml should just about get it there.
I don't have much time to play with it today but it splits easily.
declare @Mess varchar(8000) = '1|I9||NECK SHOULDER BACK PAIN||A|| 2|I9|V571|PHYSICAL THERAPY NEC||P|""|470 3|I9|7242|LUMBAGO||S1|| 4|I9|71941|JOINT PAIN-SHLDER||S2|| 5|I9|71942|JOINT PAIN-UP/ARM||S3|| 6|I9|7231|CERVICALGIA||S4|| 7|I9|7840|HEADACHE||S5|| 8|I9|9070|LT EFF INTRACRANIAL INJ||S6||'
select *
from DelimitedSplit8K(@Mess, '|')
From here just need to reassemble into columns.
--EDIT just a bit of modulus math and a pivot????

Basically the direction I am heading but with the record terminator added.

Charles Hearn SSCrazy Points: 2680 More actions · Answer 8

These look HL7 messages (particularly for ICD 9) and you should be able to find a freeware parser for them.

I think it's quite typical for messages to be stored in a table as part of the queuing system. E.g. if something happens I want to know what the original message looked like for troubleshooting purposes.

AndrewSQLDBA SSC-Insane Points: 21827 More actions · Answer 9

i am having issues with a multi part delimiter. One delimiter "$", is at the end of the string. The other delimiter "|", separates the 8 different values so that I can then insert them into a table with 8 distinct columns.

Create TABLE dbo.DiagInfo

(

SetID int

,DiagCodingMethod varchar(5)

,DiagCode varchar(250)

,DiagDescription varchar(250)

,DiagDateTime varchar(250)

,DiagDrgType varchar(250)

,MajorDiagCategory varchar(250)

,DiagRelatedGroup varchar(250)

)

This is what the data currently looks like in a single column in a table

-- Sample Data

1|I9||INTRACRANIAL INJURY||A||$2|I9|V5789|REHABILITATION PROC NEC||P|""|470$3|I9|85400|BRAIN INJURY NEC||S1||$

1|I9||SACROILIAC JOINT PAIN||A||$2|I9|V571|PHYSICAL THERAPY NEC||P|""|470$3|I9|7246|DISORDERS OF SACRUM||S1||$4|I9|V222|PREG STATE, INCIDENTAL||S2||$

1|I9||STRESS INCONTINENCE||A||$2|I9|V571|PHYSICAL THERAPY NEC||P|""|470$3|I9|6256|FEM STRESS INCONTINENCE||S1||$

1|I9||PELVIC FLOOR SYNDROME||A||$2|I9|V571|PHYSICAL THERAPY NEC||P|""|470$3|I9|72999|SOFT TISSUE DISORDER NEC||S1||$4|I9|6250|DYSPAREUNIA||S2||$

1|I9||POOR PELVIC FLOOR CONTROL||A||$2|I9|V571|PHYSICAL THERAPY NEC||P|""|470$3|I9|7291|MYALGIA AND MYOSITIS NOS||S1||$4|I9|7822|LOCAL SUPRFICIAL SWELLNG||S2||$5|I9|72990|DISORDER SFT TISSUE NOS||S3||$

1|I9||ABD PAIN||A||$2|I9|V571|PHYSICAL THERAPY NEC||P|""|470$3|I9|78900|ABD PAIN UNSPEC SITE||S1||$4|I9|7291|MYALGIA AND MYOSITIS NOS||S2||$5|I9|78659|CHEST PAIN NEC||S3||$6|I9|V4589|POSTSURGICAL STATES NEC||S4||$

1|I9||ADHESIVE CAPSULITIS||A||$2|I9|V571|PHYSICAL THERAPY NEC||P|""|470$3|I9|7260|ADHESIVE CAPSULIT SHLDER||S1||$

1|I9||CERVICAL STRAIN||A||$2|I9|V571|PHYSICAL THERAPY NEC||P|""|470$3|I9|8470|SPRAIN OF NECK||S1||$

1|I9||R RC TENDONITIS||A||$2|I9|V571|PHYSICAL THERAPY NEC||P|""|470$3|I9|8404|SPRAIN ROTATOR CUFF||S1||$

1|I9||KNEE PAIN||A||$2|I9|V571|PHYSICAL THERAPY NEC||P|""|470$3|I9|71535|LOC OSTEOARTH NOS-PELVIS||S1||$4|I9|71946|PAIN IN JOINT-L/LEG||S2||$

1|I9||JRA END STAGE DX||A||$2|I9|V571|PHYSICAL THERAPY NEC||P|""|470$3|I9|V5481|AFTRCR FOLL JOINT REPLAC||S1||$4|I9|V4365|KNEE JT REPLACE STATUS||S2||$

1|I9||NECK PAIN S/P MVA||A||$2|I9|V571|PHYSICAL THERAPY NEC||P|""|470$3|I9|7231|CERVICALGIA||S1||$4|I9|8470|SPRAIN OF NECK||S2||$5|I9|8471|SPRAIN THORACIC REGION||S3||$

1|I9||LBP||A||$2|I9|V571|PHYSICAL THERAPY NEC||P|""|470$3|I9|7242|LUMBAGO||S1||$

1|I9||NECK SPRAIN, CERVICALGIA||A||$2|I9|V571|PHYSICAL THERAPY NEC||P|""|470$3|I9|8470|SPRAIN OF NECK||S1||$4|I9|7231|CERVICALGIA||S2||$5|I9|33921|AC POST-TRAUMA HEADACHE||S3||$6|I9|35579|MONONEURITIS LOW LIMB||S4||$

1|I9||NECK PAIN||A||$2|I9|V571|PHYSICAL THERAPY NEC||P|""|470$3|I9|7234|BRACHIAL NEURITIS NOS||S1||$4|I9|71943|JOINT PAIN-FOREARM||S2||$

1|I9||LLE STRENGTHENING||A||$2|I9|V5789|REHABILITATION PROC NEC||P|""|470$3|I9|7213|LUMBOSACRAL SPONDYLOSIS||S1||$4|I9|72989|MUSCSKEL SYMPT LIMB NEC||S2||$5|I9|7812|ABNORMALITY OF GAIT||S3||$

1|I9||CERVICAL STRAIN||A||$2|I9|V571|PHYSICAL THERAPY NEC||P|""|470$3|I9|8470|SPRAIN OF NECK||S1||$4|I9|8471|SPRAIN THORACIC REGION||S2||$5|I9|7231|CERVICALGIA||S3||$6|I9|7242|LUMBAGO||S4||$

1|I9||HIP DJD||A||$2|I9|V571|PHYSICAL THERAPY NEC||P|""|470$3|I9|71595|OSTEOARTHROS NOS-PELVIS||S1||$

1|I9||ANKLE PAIN||A||$2|I9|V571|PHYSICAL THERAPY NEC||P|""|470$3|I9|8248|FX ANKLE NOS-CLOSED||S1||$

1|I9||NECK SHOULDER BACK PAIN||A||$2|I9|V571|PHYSICAL THERAPY NEC||P|""|470$3|I9|7242|LUMBAGO||S1||$4|I9|71941|JOINT PAIN-SHLDER||S2||$5|I9|71942|JOINT PAIN-UP/ARM||S3||$6|I9|7231|CERVICALGIA||S4||$7|I9|7840|HEADACHE||S5||$8|I9|9070|LT EFF INTRACRANIAL INJ||S6||$

1|I9||SHOULDER PAIN||A||$2|I9|V571|PHYSICAL THERAPY NEC||P|""|470$3|I9|71941|JOINT PAIN-SHLDER||S1||$

1|I9||ANKLE PAIN||A||$

1|I9||R CALF PAIN||A||$

I am able to split the data on the "$", but I need to remove the "$" at the end of the string. The split function that I am using causes a blank row to be created, because it thinks there is more data past the last "$" in the string.

This is what I need the data to look like after executing the Split function, and store the data in a table variable, or a Temp table. It can be stored in a single column for now.

1|I9||INTRACRANIAL INJURY||A||

2|I9|V5789|REHABILITATION PROC NEC||P|""|470

3|I9|85400|BRAIN INJURY NEC||S1||

1|I9||SACROILIAC JOINT PAIN||A||

2|I9|V571|PHYSICAL THERAPY NEC||P|""|470

3|I9|7246|DISORDERS OF SACRUM||S1||

4|I9|V222|PREG STATE, INCIDENTAL||S2||

1|I9||STRESS INCONTINENCE||A||

2|I9|V571|PHYSICAL THERAPY NEC||P|""|470

3|I9|6256|FEM STRESS INCONTINENCE||S1||

1|I9||PELVIC FLOOR SYNDROME||A||

2|I9|V571|PHYSICAL THERAPY NEC||P|""|470

3|I9|72999|SOFT TISSUE DISORDER NEC||S1||

4|I9|6250|DYSPAREUNIA||S2||

1|I9||POOR PELVIC FLOOR CONTROL||A||

2|I9|V571|PHYSICAL THERAPY NEC||P|""|470

3|I9|7291|MYALGIA AND MYOSITIS NOS||S1||

4|I9|7822|LOCAL SUPRFICIAL SWELLNG||S2||

5|I9|72990|DISORDER SFT TISSUE NOS||S3||

1|I9||ABD PAIN||A||

2|I9|V571|PHYSICAL THERAPY NEC||P|""|470

3|I9|78900|ABD PAIN UNSPEC SITE||S1||

4|I9|7291|MYALGIA AND MYOSITIS NOS||S2||

5|I9|78659|CHEST PAIN NEC||S3||

6|I9|V4589|POSTSURGICAL STATES NEC||S4||

1|I9||ADHESIVE CAPSULITIS||A||

2|I9|V571|PHYSICAL THERAPY NEC||P|""|470

3|I9|7260|ADHESIVE CAPSULIT SHLDER||S1||

1|I9||CERVICAL STRAIN||A||

2|I9|V571|PHYSICAL THERAPY NEC||P|""|470

3|I9|8470|SPRAIN OF NECK||S1||

1|I9||R RC TENDONITIS||A||

2|I9|V571|PHYSICAL THERAPY NEC||P|""|470

3|I9|8404|SPRAIN ROTATOR CUFF||S1||

1|I9||KNEE PAIN||A||

2|I9|V571|PHYSICAL THERAPY NEC||P|""|470

3|I9|71535|LOC OSTEOARTH NOS-PELVIS||S1||

4|I9|71946|PAIN IN JOINT-L/LEG||S2||

1|I9||JRA END STAGE DX||A||

2|I9|V571|PHYSICAL THERAPY NEC||P|""|470

3|I9|V5481|AFTRCR FOLL JOINT REPLAC||S1||

4|I9|V4365|KNEE JT REPLACE STATUS||S2||

1|I9||NECK PAIN S/P MVA||A||

2|I9|V571|PHYSICAL THERAPY NEC||P|""|470

3|I9|7231|CERVICALGIA||S1||

4|I9|8470|SPRAIN OF NECK||S2||

5|I9|8471|SPRAIN THORACIC REGION||S3||

1|I9||LBP||A||

2|I9|V571|PHYSICAL THERAPY NEC||P|""|470

3|I9|7242|LUMBAGO||S1||

1|I9||NECK SPRAIN, CERVICALGIA||A||

2|I9|V571|PHYSICAL THERAPY NEC||P|""|470

3|I9|8470|SPRAIN OF NECK||S1||

4|I9|7231|CERVICALGIA||S2||

5|I9|33921|AC POST-TRAUMA HEADACHE||S3||

6|I9|35579|MONONEURITIS LOW LIMB||S4||

1|I9||NECK PAIN||A||

2|I9|V571|PHYSICAL THERAPY NEC||P|""|470

3|I9|7234|BRACHIAL NEURITIS NOS||S1||

4|I9|71943|JOINT PAIN-FOREARM||S2||

1|I9||LLE STRENGTHENING||A||

2|I9|V5789|REHABILITATION PROC NEC||P|""|470

3|I9|7213|LUMBOSACRAL SPONDYLOSIS||S1||

4|I9|72989|MUSCSKEL SYMPT LIMB NEC||S2||

5|I9|7812|ABNORMALITY OF GAIT||S3||

1|I9||CERVICAL STRAIN||A||

2|I9|V571|PHYSICAL THERAPY NEC||P|""|470

3|I9|8470|SPRAIN OF NECK||S1||

4|I9|8471|SPRAIN THORACIC REGION||S2||

5|I9|7231|CERVICALGIA||S3||

6|I9|7242|LUMBAGO||S4||

1|I9||HIP DJD||A||

2|I9|V571|PHYSICAL THERAPY NEC||P|""|470

3|I9|71595|OSTEOARTHROS NOS-PELVIS||S1||

1|I9||ANKLE PAIN||A||

2|I9|V571|PHYSICAL THERAPY NEC||P|""|470

3|I9|8248|FX ANKLE NOS-CLOSED||S1||

1|I9||NECK SHOULDER BACK PAIN||A||

2|I9|V571|PHYSICAL THERAPY NEC||P|""|470

3|I9|7242|LUMBAGO||S1||

4|I9|71941|JOINT PAIN-SHLDER||S2||

5|I9|71942|JOINT PAIN-UP/ARM||S3||

6|I9|7231|CERVICALGIA||S4||

7|I9|7840|HEADACHE||S5||

8|I9|9070|LT EFF INTRACRANIAL INJ||S6||

1|I9||SHOULDER PAIN||A||

2|I9|V571|PHYSICAL THERAPY NEC||P|""|470

3|I9|71941|JOINT PAIN-SHLDER||S1||

1|I9||ANKLE PAIN||A||

1|I9||R CALF PAIN||A||

The next step is to split the data on the "|" delimiter, and insert into a table with 8 distinct columns, I listed a Create Table statement above. If there is nothing between the Pipe delimiters, then insert a NULL value. As you can see in the sample data, there may or may not be data after the last delimiter.

2|I9|V571|PHYSICAL THERAPY NEC||P|""|470

I would like to be able to perform this all within SQL, using T-SQL if possible. I can use C# in a SSIS package if necessary.

Thanks for any and all help with this.

Andrew SQLDBA

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 10

I can't work on it here at work. I'll look at it when I get home and can concentrate on the solution.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 11

Here is what I really need to help. The DDL for the original table with the data in it that you need to parse. Sample data for that table, 3 or 4 rows as insert statements to populate the table. The DDL for the destination tables. The expected results in the destination tables based on the sample data.

I have done this before, but with things broken down as you have it is actually harder to put together the code you need.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 12

AndrewSQLDBA (10/7/2011)
i am having issues with a multi part delimiter. One delimiter "$", is at the end of the string. The other delimiter "|", separates the 8 different values so that I can then insert them into a table with 8 distinct columns.
Create TABLE dbo.DiagInfo
(
SetID int
,DiagCodingMethod varchar(5)
,DiagCode varchar(250)
,DiagDescription varchar(250)
,DiagDateTime varchar(250)
,DiagDrgType varchar(250)
,MajorDiagCategory varchar(250)
,DiagRelatedGroup varchar(250)
)
This is what the data currently looks like in a single column in a table
-- Sample Data
1|I9||INTRACRANIAL INJURY||A||$2|I9|V5789|REHABILITATION PROC NEC||P|""|470$3|I9|85400|BRAIN INJURY NEC||S1||$
1|I9||SACROILIAC JOINT PAIN||A||$2|I9|V571|PHYSICAL THERAPY NEC||P|""|470$3|I9|7246|DISORDERS OF SACRUM||S1||$4|I9|V222|PREG STATE, INCIDENTAL||S2||$
1|I9||STRESS INCONTINENCE||A||$2|I9|V571|PHYSICAL THERAPY NEC||P|""|470$3|I9|6256|FEM STRESS INCONTINENCE||S1||$
1|I9||PELVIC FLOOR SYNDROME||A||$2|I9|V571|PHYSICAL THERAPY NEC||P|""|470$3|I9|72999|SOFT TISSUE DISORDER NEC||S1||$4|I9|6250|DYSPAREUNIA||S2||$
1|I9||POOR PELVIC FLOOR CONTROL||A||$2|I9|V571|PHYSICAL THERAPY NEC||P|""|470$3|I9|7291|MYALGIA AND MYOSITIS NOS||S1||$4|I9|7822|LOCAL SUPRFICIAL SWELLNG||S2||$5|I9|72990|DISORDER SFT TISSUE NOS||S3||$
1|I9||ABD PAIN||A||$2|I9|V571|PHYSICAL THERAPY NEC||P|""|470$3|I9|78900|ABD PAIN UNSPEC SITE||S1||$4|I9|7291|MYALGIA AND MYOSITIS NOS||S2||$5|I9|78659|CHEST PAIN NEC||S3||$6|I9|V4589|POSTSURGICAL STATES NEC||S4||$
1|I9||ADHESIVE CAPSULITIS||A||$2|I9|V571|PHYSICAL THERAPY NEC||P|""|470$3|I9|7260|ADHESIVE CAPSULIT SHLDER||S1||$
1|I9||CERVICAL STRAIN||A||$2|I9|V571|PHYSICAL THERAPY NEC||P|""|470$3|I9|8470|SPRAIN OF NECK||S1||$
1|I9||R RC TENDONITIS||A||$2|I9|V571|PHYSICAL THERAPY NEC||P|""|470$3|I9|8404|SPRAIN ROTATOR CUFF||S1||$
1|I9||KNEE PAIN||A||$2|I9|V571|PHYSICAL THERAPY NEC||P|""|470$3|I9|71535|LOC OSTEOARTH NOS-PELVIS||S1||$4|I9|71946|PAIN IN JOINT-L/LEG||S2||$
1|I9||JRA END STAGE DX||A||$2|I9|V571|PHYSICAL THERAPY NEC||P|""|470$3|I9|V5481|AFTRCR FOLL JOINT REPLAC||S1||$4|I9|V4365|KNEE JT REPLACE STATUS||S2||$
1|I9||NECK PAIN S/P MVA||A||$2|I9|V571|PHYSICAL THERAPY NEC||P|""|470$3|I9|7231|CERVICALGIA||S1||$4|I9|8470|SPRAIN OF NECK||S2||$5|I9|8471|SPRAIN THORACIC REGION||S3||$
1|I9||LBP||A||$2|I9|V571|PHYSICAL THERAPY NEC||P|""|470$3|I9|7242|LUMBAGO||S1||$
1|I9||NECK SPRAIN, CERVICALGIA||A||$2|I9|V571|PHYSICAL THERAPY NEC||P|""|470$3|I9|8470|SPRAIN OF NECK||S1||$4|I9|7231|CERVICALGIA||S2||$5|I9|33921|AC POST-TRAUMA HEADACHE||S3||$6|I9|35579|MONONEURITIS LOW LIMB||S4||$
1|I9||NECK PAIN||A||$2|I9|V571|PHYSICAL THERAPY NEC||P|""|470$3|I9|7234|BRACHIAL NEURITIS NOS||S1||$4|I9|71943|JOINT PAIN-FOREARM||S2||$
1|I9||LLE STRENGTHENING||A||$2|I9|V5789|REHABILITATION PROC NEC||P|""|470$3|I9|7213|LUMBOSACRAL SPONDYLOSIS||S1||$4|I9|72989|MUSCSKEL SYMPT LIMB NEC||S2||$5|I9|7812|ABNORMALITY OF GAIT||S3||$
1|I9||CERVICAL STRAIN||A||$2|I9|V571|PHYSICAL THERAPY NEC||P|""|470$3|I9|8470|SPRAIN OF NECK||S1||$4|I9|8471|SPRAIN THORACIC REGION||S2||$5|I9|7231|CERVICALGIA||S3||$6|I9|7242|LUMBAGO||S4||$
1|I9||HIP DJD||A||$2|I9|V571|PHYSICAL THERAPY NEC||P|""|470$3|I9|71595|OSTEOARTHROS NOS-PELVIS||S1||$
1|I9||ANKLE PAIN||A||$2|I9|V571|PHYSICAL THERAPY NEC||P|""|470$3|I9|8248|FX ANKLE NOS-CLOSED||S1||$
1|I9||NECK SHOULDER BACK PAIN||A||$2|I9|V571|PHYSICAL THERAPY NEC||P|""|470$3|I9|7242|LUMBAGO||S1||$4|I9|71941|JOINT PAIN-SHLDER||S2||$5|I9|71942|JOINT PAIN-UP/ARM||S3||$6|I9|7231|CERVICALGIA||S4||$7|I9|7840|HEADACHE||S5||$8|I9|9070|LT EFF INTRACRANIAL INJ||S6||$
1|I9||SHOULDER PAIN||A||$2|I9|V571|PHYSICAL THERAPY NEC||P|""|470$3|I9|71941|JOINT PAIN-SHLDER||S1||$
1|I9||ANKLE PAIN||A||$
1|I9||R CALF PAIN||A||$
I am able to split the data on the "$", but I need to remove the "$" at the end of the string. The split function that I am using causes a blank row to be created, because it thinks there is more data past the last "$" in the string.
This is what I need the data to look like after executing the Split function, and store the data in a table variable, or a Temp table. It can be stored in a single column for now.
1|I9||INTRACRANIAL INJURY||A||
2|I9|V5789|REHABILITATION PROC NEC||P|""|470
3|I9|85400|BRAIN INJURY NEC||S1||
1|I9||SACROILIAC JOINT PAIN||A||
2|I9|V571|PHYSICAL THERAPY NEC||P|""|470
3|I9|7246|DISORDERS OF SACRUM||S1||
4|I9|V222|PREG STATE, INCIDENTAL||S2||
1|I9||STRESS INCONTINENCE||A||
2|I9|V571|PHYSICAL THERAPY NEC||P|""|470
3|I9|6256|FEM STRESS INCONTINENCE||S1||
1|I9||PELVIC FLOOR SYNDROME||A||
2|I9|V571|PHYSICAL THERAPY NEC||P|""|470
3|I9|72999|SOFT TISSUE DISORDER NEC||S1||
4|I9|6250|DYSPAREUNIA||S2||
1|I9||POOR PELVIC FLOOR CONTROL||A||
2|I9|V571|PHYSICAL THERAPY NEC||P|""|470
3|I9|7291|MYALGIA AND MYOSITIS NOS||S1||
4|I9|7822|LOCAL SUPRFICIAL SWELLNG||S2||
5|I9|72990|DISORDER SFT TISSUE NOS||S3||
1|I9||ABD PAIN||A||
2|I9|V571|PHYSICAL THERAPY NEC||P|""|470
3|I9|78900|ABD PAIN UNSPEC SITE||S1||
4|I9|7291|MYALGIA AND MYOSITIS NOS||S2||
5|I9|78659|CHEST PAIN NEC||S3||
6|I9|V4589|POSTSURGICAL STATES NEC||S4||
1|I9||ADHESIVE CAPSULITIS||A||
2|I9|V571|PHYSICAL THERAPY NEC||P|""|470
3|I9|7260|ADHESIVE CAPSULIT SHLDER||S1||
1|I9||CERVICAL STRAIN||A||
2|I9|V571|PHYSICAL THERAPY NEC||P|""|470
3|I9|8470|SPRAIN OF NECK||S1||
1|I9||R RC TENDONITIS||A||
2|I9|V571|PHYSICAL THERAPY NEC||P|""|470
3|I9|8404|SPRAIN ROTATOR CUFF||S1||
1|I9||KNEE PAIN||A||
2|I9|V571|PHYSICAL THERAPY NEC||P|""|470
3|I9|71535|LOC OSTEOARTH NOS-PELVIS||S1||
4|I9|71946|PAIN IN JOINT-L/LEG||S2||
1|I9||JRA END STAGE DX||A||
2|I9|V571|PHYSICAL THERAPY NEC||P|""|470
3|I9|V5481|AFTRCR FOLL JOINT REPLAC||S1||
4|I9|V4365|KNEE JT REPLACE STATUS||S2||
1|I9||NECK PAIN S/P MVA||A||
2|I9|V571|PHYSICAL THERAPY NEC||P|""|470
3|I9|7231|CERVICALGIA||S1||
4|I9|8470|SPRAIN OF NECK||S2||
5|I9|8471|SPRAIN THORACIC REGION||S3||
1|I9||LBP||A||
2|I9|V571|PHYSICAL THERAPY NEC||P|""|470
3|I9|7242|LUMBAGO||S1||
1|I9||NECK SPRAIN, CERVICALGIA||A||
2|I9|V571|PHYSICAL THERAPY NEC||P|""|470
3|I9|8470|SPRAIN OF NECK||S1||
4|I9|7231|CERVICALGIA||S2||
5|I9|33921|AC POST-TRAUMA HEADACHE||S3||
6|I9|35579|MONONEURITIS LOW LIMB||S4||
1|I9||NECK PAIN||A||
2|I9|V571|PHYSICAL THERAPY NEC||P|""|470
3|I9|7234|BRACHIAL NEURITIS NOS||S1||
4|I9|71943|JOINT PAIN-FOREARM||S2||
1|I9||LLE STRENGTHENING||A||
2|I9|V5789|REHABILITATION PROC NEC||P|""|470
3|I9|7213|LUMBOSACRAL SPONDYLOSIS||S1||
4|I9|72989|MUSCSKEL SYMPT LIMB NEC||S2||
5|I9|7812|ABNORMALITY OF GAIT||S3||
1|I9||CERVICAL STRAIN||A||
2|I9|V571|PHYSICAL THERAPY NEC||P|""|470
3|I9|8470|SPRAIN OF NECK||S1||
4|I9|8471|SPRAIN THORACIC REGION||S2||
5|I9|7231|CERVICALGIA||S3||
6|I9|7242|LUMBAGO||S4||
1|I9||HIP DJD||A||
2|I9|V571|PHYSICAL THERAPY NEC||P|""|470
3|I9|71595|OSTEOARTHROS NOS-PELVIS||S1||
1|I9||ANKLE PAIN||A||
2|I9|V571|PHYSICAL THERAPY NEC||P|""|470
3|I9|8248|FX ANKLE NOS-CLOSED||S1||
1|I9||NECK SHOULDER BACK PAIN||A||
2|I9|V571|PHYSICAL THERAPY NEC||P|""|470
3|I9|7242|LUMBAGO||S1||
4|I9|71941|JOINT PAIN-SHLDER||S2||
5|I9|71942|JOINT PAIN-UP/ARM||S3||
6|I9|7231|CERVICALGIA||S4||
7|I9|7840|HEADACHE||S5||
8|I9|9070|LT EFF INTRACRANIAL INJ||S6||
1|I9||SHOULDER PAIN||A||
2|I9|V571|PHYSICAL THERAPY NEC||P|""|470
3|I9|71941|JOINT PAIN-SHLDER||S1||
1|I9||ANKLE PAIN||A||
1|I9||R CALF PAIN||A||
The next step is to split the data on the "|" delimiter, and insert into a table with 8 distinct columns, I listed a Create Table statement above. If there is nothing between the Pipe delimiters, then insert a NULL value. As you can see in the sample data, there may or may not be data after the last delimiter.
2|I9|V571|PHYSICAL THERAPY NEC||P|""|470
I would like to be able to perform this all within SQL, using T-SQL if possible. I can use C# in a SSIS package if necessary.
Thanks for any and all help with this.
Andrew SQLDBA

Andrew, you've been around here long enough to know how to post data correctly on this forum. ;-)Please read and heed the article at the first link in my signature line below before you post again. Ultimately, it'll entice other people to help you more quickly and to give you better answers.

Here's one way you could have posted some "readily consumable data"...

--=====================================================================================================================

-- Create a test table from the given data.

-- Nothing in this section is a part of the solution. We're just creating test data to demo with here.

--=====================================================================================================================

--===== Conditionally drop the test table to make reruns in SSMS easier

IF OBJECT_ID('TempDB..#JBMTest','U') IS NOT NULL DROP TABLE #JBMTest

;

--===== Create and populate the test table on the fly

SELECT SplitMe = CAST(testdata.SplitMe AS VARCHAR(8000))

INTO #JBMTest

FROM (

SELECT '1|I9||INTRACRANIAL INJURY||A||$2|I9|V5789|REHABILITATION PROC NEC||P|""|470$3|I9|85400|BRAIN INJURY NEC||S1||$' UNION ALL

SELECT '1|I9||SACROILIAC JOINT PAIN||A||$2|I9|V571|PHYSICAL THERAPY NEC||P|""|470$3|I9|7246|DISORDERS OF SACRUM||S1||$4|I9|V222|PREG STATE, INCIDENTAL||S2||$' UNION ALL

SELECT '1|I9||STRESS INCONTINENCE||A||$2|I9|V571|PHYSICAL THERAPY NEC||P|""|470$3|I9|6256|FEM STRESS INCONTINENCE||S1||$' UNION ALL

SELECT '1|I9||PELVIC FLOOR SYNDROME||A||$2|I9|V571|PHYSICAL THERAPY NEC||P|""|470$3|I9|72999|SOFT TISSUE DISORDER NEC||S1||$4|I9|6250|DYSPAREUNIA||S2||$' UNION ALL

SELECT '1|I9||POOR PELVIC FLOOR CONTROL||A||$2|I9|V571|PHYSICAL THERAPY NEC||P|""|470$3|I9|7291|MYALGIA AND MYOSITIS NOS||S1||$4|I9|7822|LOCAL SUPRFICIAL SWELLNG||S2||$5|I9|72990|DISORDER SFT TISSUE NOS||S3||$' UNION ALL

SELECT '1|I9||ABD PAIN||A||$2|I9|V571|PHYSICAL THERAPY NEC||P|""|470$3|I9|78900|ABD PAIN UNSPEC SITE||S1||$4|I9|7291|MYALGIA AND MYOSITIS NOS||S2||$5|I9|78659|CHEST PAIN NEC||S3||$6|I9|V4589|POSTSURGICAL STATES NEC||S4||$' UNION ALL

SELECT '1|I9||ADHESIVE CAPSULITIS||A||$2|I9|V571|PHYSICAL THERAPY NEC||P|""|470$3|I9|7260|ADHESIVE CAPSULIT SHLDER||S1||$' UNION ALL

SELECT '1|I9||CERVICAL STRAIN||A||$2|I9|V571|PHYSICAL THERAPY NEC||P|""|470$3|I9|8470|SPRAIN OF NECK||S1||$' UNION ALL

SELECT '1|I9||R RC TENDONITIS||A||$2|I9|V571|PHYSICAL THERAPY NEC||P|""|470$3|I9|8404|SPRAIN ROTATOR CUFF||S1||$' UNION ALL

SELECT '1|I9||KNEE PAIN||A||$2|I9|V571|PHYSICAL THERAPY NEC||P|""|470$3|I9|71535|LOC OSTEOARTH NOS-PELVIS||S1||$4|I9|71946|PAIN IN JOINT-L/LEG||S2||$' UNION ALL

SELECT '1|I9||JRA END STAGE DX||A||$2|I9|V571|PHYSICAL THERAPY NEC||P|""|470$3|I9|V5481|AFTRCR FOLL JOINT REPLAC||S1||$4|I9|V4365|KNEE JT REPLACE STATUS||S2||$' UNION ALL

SELECT '1|I9||NECK PAIN S/P MVA||A||$2|I9|V571|PHYSICAL THERAPY NEC||P|""|470$3|I9|7231|CERVICALGIA||S1||$4|I9|8470|SPRAIN OF NECK||S2||$5|I9|8471|SPRAIN THORACIC REGION||S3||$' UNION ALL

SELECT '1|I9||LBP||A||$2|I9|V571|PHYSICAL THERAPY NEC||P|""|470$3|I9|7242|LUMBAGO||S1||$' UNION ALL

SELECT '1|I9||NECK SPRAIN, CERVICALGIA||A||$2|I9|V571|PHYSICAL THERAPY NEC||P|""|470$3|I9|8470|SPRAIN OF NECK||S1||$4|I9|7231|CERVICALGIA||S2||$5|I9|33921|AC POST-TRAUMA HEADACHE||S3||$6|I9|35579|MONONEURITIS LOW LIMB||S4||$' UNION ALL

SELECT '1|I9||NECK PAIN||A||$2|I9|V571|PHYSICAL THERAPY NEC||P|""|470$3|I9|7234|BRACHIAL NEURITIS NOS||S1||$4|I9|71943|JOINT PAIN-FOREARM||S2||$' UNION ALL

SELECT '1|I9||LLE STRENGTHENING||A||$2|I9|V5789|REHABILITATION PROC NEC||P|""|470$3|I9|7213|LUMBOSACRAL SPONDYLOSIS||S1||$4|I9|72989|MUSCSKEL SYMPT LIMB NEC||S2||$5|I9|7812|ABNORMALITY OF GAIT||S3||$' UNION ALL

SELECT '1|I9||CERVICAL STRAIN||A||$2|I9|V571|PHYSICAL THERAPY NEC||P|""|470$3|I9|8470|SPRAIN OF NECK||S1||$4|I9|8471|SPRAIN THORACIC REGION||S2||$5|I9|7231|CERVICALGIA||S3||$6|I9|7242|LUMBAGO||S4||$' UNION ALL

SELECT '1|I9||HIP DJD||A||$2|I9|V571|PHYSICAL THERAPY NEC||P|""|470$3|I9|71595|OSTEOARTHROS NOS-PELVIS||S1||$' UNION ALL

SELECT '1|I9||ANKLE PAIN||A||$2|I9|V571|PHYSICAL THERAPY NEC||P|""|470$3|I9|8248|FX ANKLE NOS-CLOSED||S1||$' UNION ALL

SELECT '1|I9||NECK SHOULDER BACK PAIN||A||$2|I9|V571|PHYSICAL THERAPY NEC||P|""|470$3|I9|7242|LUMBAGO||S1||$4|I9|71941|JOINT PAIN-SHLDER||S2||$5|I9|71942|JOINT PAIN-UP/ARM||S3||$6|I9|7231|CERVICALGIA||S4||$7|I9|7840|HEADACHE||S5||$8|I9|9070|LT EFF INTRACRANIAL INJ||S6||$' UNION ALL

SELECT '1|I9||SHOULDER PAIN||A||$2|I9|V571|PHYSICAL THERAPY NEC||P|""|470$3|I9|71941|JOINT PAIN-SHLDER||S1||$' UNION ALL

SELECT '1|I9||ANKLE PAIN||A||$' UNION ALL

SELECT '1|I9||R CALF PAIN||A||$'

) testdata (SplitMe)

;

And, now for the solution... first, here's a link to an article that explains the method a bit insofar as the use of "modulus" and "integer division" goes...

http://www.sqlservercentral.com/articles/T-SQL/63003/

... and here's a nice, short solution... as usual, the details are in the comments...

[font="Arial Black"]{EDIT} Actually, the "All-in-One" factor causes terrible performance on the following code. Please don't use the following code. I'll be back in a bit with some nice "Divide'n'Conquer" code.[/font]

WITH

cteEnumerateAndClean AS

( --=== Assign the "SetID" to each row and cleanup the "bad" delimiters.

-- Since each "SubSet" will always have 8 items, we can change the "$"

-- to a "|" to keep things simple. The STUFF function get's rid of the

-- last "$".

SELECT SetID = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)),

SplitMe = REPLACE(STUFF(SplitMe,LEN(SplitMe),1,''),'$','|')

FROM #JBMTest

),

cteSplitAndEnumerate AS

( --=== Split the data and number the elements for 8 fixed columns per row

SELECT SetID, --basically, a row number

SubSetNumber = (split.ItemNumber-1)/8+1, --groups of 8 items on each row of input

ElementNumber = (split.ItemNumber-1)%8, --the split items are numbered 0 to 7

split.Item --the item split out from pipes

FROM cteEnumerateAndClean

CROSS APPLY DelimitedSplit8K(SplitMe,'|') split

) --=== Pivot the data that we split out into the correct columns.

-- This uses a very high speed, conventional "CROSS TAB" to do the Pivot.

SELECT SetID,

SubSetNumber,

DiagCodingMethod = MAX(CASE WHEN ElementNumber = 1 THEN Item ELSE '' END),

DiagCode = MAX(CASE WHEN ElementNumber = 2 THEN Item ELSE '' END),

DiagDescription = MAX(CASE WHEN ElementNumber = 3 THEN Item ELSE '' END),

DiagDateTime = MAX(CASE WHEN ElementNumber = 4 THEN Item ELSE '' END),

DiagDrgType = MAX(CASE WHEN ElementNumber = 5 THEN Item ELSE '' END),

MajorDiagCategory = MAX(CASE WHEN ElementNumber = 6 THEN Item ELSE '' END),

DiagRelatedGroup = MAX(CASE WHEN ElementNumber = 7 THEN Item ELSE '' END)

FROM cteSplitAndEnumerate

GROUP BY SetID, SubSetNumber

ORDER BY SetID, SubSetNumber

;

Do read the article I pointed you to on how to post data. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 13

Almost forgot... you can get the code for the DelimitedSplit8K function from the "Resources" section of the following article...

http://www.sqlservercentral.com/articles/Tally+Table/72993/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 14

The following code runs in about 1/5th of the time of the previous code. All I did was separate out the CTE's into invidual pieces of code that populated their own temp table. Gotta love this wonderful '50s techonology known as "Divide'n'Conquer"...

--drop table #MyHead, #MyHead1, #MyHead2

SELECT SetID = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)),

SplitMe = REPLACE(STUFF(SplitMe,LEN(SplitMe),1,''),'$','|')

INTO #MyHead

FROM #JBMTest

SELECT SetID = ISNULL(SetID,0), --basically, a row number

SubSetNumber = ISNULL((split.ItemNumber-1)/8+1,0), --groups of 8 items on each row of input

ElementNumber = ISNULL((split.ItemNumber-1)%8,0), --the split items are numbered 0 to 7

split.Item --the item split out from pipes

INTO #Myhead1

FROM #MyHead

CROSS APPLY DelimitedSplit8K(SplitMe,'|') split

SELECT SetID,

SubSetNumber,

DiagCodingMethod = MAX(CASE WHEN ElementNumber = 1 THEN Item ELSE '' END),

DiagCode = MAX(CASE WHEN ElementNumber = 2 THEN Item ELSE '' END),

DiagDescription = MAX(CASE WHEN ElementNumber = 3 THEN Item ELSE '' END),

DiagDateTime = MAX(CASE WHEN ElementNumber = 4 THEN Item ELSE '' END),

DiagDrgType = MAX(CASE WHEN ElementNumber = 5 THEN Item ELSE '' END),

MajorDiagCategory = MAX(CASE WHEN ElementNumber = 6 THEN Item ELSE '' END),

DiagRelatedGroup = MAX(CASE WHEN ElementNumber = 7 THEN Item ELSE '' END)

INTO #MyHead2

FROM #Myhead1

GROUP BY SetID, SubSetNumber

ORDER BY SetID, SubSetNumber

;

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)