Split data from strings into columns

This data looks like a table itself. It could have been stored in SQL Server as xml. SQL Server supports xml fields and allows querying them. In fact, one could try to convert this string to XML, then try to query it:

declare @medTable table (item nvarchar(2000))
insert into @medTable
values ('VS5~MedCond1~35.4|VS4~MedCond2~16|VS1~MedCond3~155|VS2~MedCond4~70|SPO2~MedCond5~100|VS3~MedCond6~64|FiO2~MedCond7~21|MAP~MedCond8~98|');

-- Step 1: Replace `|` with <item> tags and `~` with `tag` tags
-- This will return an xml value for each medTable row
with items as (
    select xmlField= cast('<item><tag>' 
                           + replace( 
                                     replace(item,'|','</tag></item><item><tag>'),
                                    '~','</tag><tag>' )
                           + '</tag></item>' as xml) 
    from @medTable 
)
-- Step 2: Select different tags and display them as fields
select 
    y.item.value('(tag/text())[1]','nvarchar(20)'),
    y.item.value('(tag/text())[2]','nvarchar(20)'),
    y.item.value('(tag/text())[3]','nvarchar(20)')
from items outer apply xmlField.nodes('item') as y(item)

The result is :

-------------------- -------------------- -------
VS5                  MedCond1             35.4
VS4                  MedCond2             16
VS1                  MedCond3             155
VS2                  MedCond4             70
SPO2                 MedCond5             100
VS3                  MedCond6             64
FiO2                 MedCond7             21
MAP                  MedCond8             98
NULL                 NULL                 NULL

It would be better to perform this conversion when loading the data though. It's easier for example, to make the replacements in C# or SSIS and store a complete xml value in the database.

You can modify this query too, to generate the xml value and store it in the database:

declare @medTable2 table (xmlField xml)

with items as (
    select xmlField= cast('<item><tag>' + replace(replace(item,'|','</tag></item><item><tag>'),'~','</tag><tag>' ) + '</tag></item>' as xml) 
    from @medTable 
)
insert into @medTable2
select items.xmlField
from items 

-- Query the new table from now on 
select 
    y.item.value('(tag/text())[1]','nvarchar(20)'),
    y.item.value('(tag/text())[2]','nvarchar(20)'),
    y.item.value('(tag/text())[3]','nvarchar(20)')
from @medTable2 outer apply xmlField.nodes('item') as y(item)

OK, let me take a stab at this. The solution I'm outlining is not going to be purely SQL Server, however, it uses a round-trip via a text-file.

The approach uses the following steps:

1. Unpivot the data delimited by the pipe symbols (to create more than one line of output for each line of input)
2. Round-trip the data from SQL Server to a text file and back
3. Separate the data into columns on the tilde ~ symbol delimiter
4. Pivot the data back into columns

The key benefit of this approach is the unpivot operation, which allows you to handle missing columns like MedCond2 naturally by the absence of an equivalent row. It also eliminates nearly all string manipulation, save for the one REPLACE function in step 1 below.

Given a single row's contents like the following:

VS5~MedCond1~35.4|VS4~MedCond2~16|VS1~MedCond3~155|VS2~MedCond4~70|SPO2~MedCond5~100|VS3~MedCond6~64|FiO2~MedCond7~21|MAP~MedCond8~98| 

Step 1 (Unpivot): Find and replace all instances of the pipe symbol with a newline character. So, REPLACE(column, '|', CHAR(13)) will give you the following lines of text (i.e. multiple lines of text in a single database row) for a single input row:

VS5~MedCond1~35.4
VS4~MedCond2~16
VS1~MedCond3~155
VS2~MedCond4~70
SPO2~MedCond5~100
VS3~MedCond6~64
FiO2~MedCond7~21
MAP~MedCond8~98

Step 2 (Round-trip): Write the above output to a text file, using your tool of choice (SSIS, SQLCMD, etc.) and ensure that the newline character defined is the same as that used in the REPLACE command in step 1.

The purpose of this step is to concatenate multiple lines within the same row with other lines in different rows.

Note that steps 1 can be eliminated by defining the row delimiter for steps 2 & 3 as the pipe symbol. I've put in the additional step 1 using newlines only to make it easier to understand and debug.

Step 3 (Separate columns): Import the text file back into SQL Server using the same tool, and define the column delimiter as the tilde ~ symbol, row delimiter same as in steps 1/2.

ColA   MedCondTitle  MedCondValue 
------ ------------- ------------- 
VS5    MedCond1      35.4
VS4    MedCond2      16
VS1    MedCond3      155
VS2    MedCond4      70
SPO2   MedCond5      100
VS3    MedCond6      64
FiO2   MedCond7      21
MAP    MedCond8      98

Step 4 (Pivot): Now you'd have a trivially simple step of pivoting rows to columns, which can be achieved with a statement of the form:

SUM(CASE WHEN MedCondTitle='MedCond1' THEN MedCondValue ELSE 0) as MedCond1