cmpmd2 cmpmd2 -3 years ago 99
SQL Question

SQL SSM 2012 Seperate Comma and then piviot column

The original code below does not populate the dx.ThirdDiagnosisCode. I need to grab the third dx code from dx.AllDiagnosisCodes.

SELECT
txn.TransactionID
,dx.DiagnosisPrimaryCodeset
,dx.AllDiagnosisCodes
,dx.PrimaryDiagnosisCode
,dx.SecondDiagnosisCode
,dx.ThirdDiagnosisCode
,dx.FourthDiagnosisCode
,dx.FifthDiagnosisCode
,dx.SixthDiagnosisCode
,dx.SeventhDiagnosisCode
,dx.EighthDiagnosisCode
,dx.NinthDiagnosisCode
,dx.TenthDiagnosisCode
,dx.EleventhDiagnosisCode
,dx.TwelfthDiagnosisCode
INTO #TMP_1
FROM txn
INNER JOIN dx ON txn.SourceID = dx.SourceID


The dx.AllDiagnosisCodes column contains the following info:

AllDiagnosisCodes
162.5,511.9,
427.89,414.00,272.4,162.9,


Is there a way to split the codes by the comma, then piviot the infomration to the following columns in the original code?

,dx.PrimaryDiagnosisCode
,dx.SecondDiagnosisCode
,dx.ThirdDiagnosisCode
,dx.FourthDiagnosisCode
,dx.FifthDiagnosisCode
,dx.SixthDiagnosisCode
,dx.SeventhDiagnosisCode
,dx.EighthDiagnosisCode
,dx.NinthDiagnosisCode
,dx.TenthDiagnosisCode
,dx.EleventhDiagnosisCode
,dx.TwelfthDiagnosisCode


Example of what I'm looking for:

1dx 2dx 3dx 4dx
162.5 511.9
427.89 414.00 72.4 162.9


I'm currently stuck here with this code so far.

SELECT A.[DiagnosisPrimaryCodeset],
Split.a.value('.', 'VARCHAR(100)') AS String
FROM (
SELECT [DiagnosisPrimaryCodeset],
CAST ('<M>' + REPLACE([AllDiagnosisCodes], ',', '</M><M>') + '</M>' AS XML) AS String
FROM dx
) AS A
CROSS APPLY String.nodes ('/M') AS Split(a);

Answer Source

No need to go vertical. I stopped at 9dx, but you get the idea. Easy to expand

Also, I added the nullif() because I saw trailing commas

Example

Let's assume YourTable (or query) looks like this:

enter image description here

Select A.ID
      ,B.* 
 from YourTable A
 Cross Apply (
                Select [1dx] = nullif(xDim.value('/x[1]','varchar(max)'),'')
                      ,[2dx] = nullif(xDim.value('/x[2]','varchar(max)'),'')
                      ,[3dx] = nullif(xDim.value('/x[3]','varchar(max)'),'')
                      ,[4dx] = nullif(xDim.value('/x[4]','varchar(max)'),'')
                      ,[5dx] = nullif(xDim.value('/x[5]','varchar(max)'),'')
                      ,[6dx] = nullif(xDim.value('/x[6]','varchar(max)'),'')
                      ,[7dx] = nullif(xDim.value('/x[7]','varchar(max)'),'')
                      ,[8dx] = nullif(xDim.value('/x[8]','varchar(max)'),'')
                      ,[9dx] = nullif(xDim.value('/x[9]','varchar(max)'),'')
                From  (Select Cast('<x>' + replace(A.AllDiagnosisCodes,',','</x><x>')+'</x>' as xml) as xDim) as A 
             ) B

Returns

enter image description here

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download