Shark-Neil Felyx Shark-Neil Felyx - 6 months ago 27
SQL Question

Breaking Down Combined Column Values into Different Rows

I have a pharmacy table that has PharmacyID and Regimen as below;

PharmacyID Regimen
140646 3TC/D4T/EFV
140653 ABC/D4T/NVP


My desire is to get something like this;

PharmacyID Regimen
140646 3TC
140646 D4T
140646 EFV
140653 ABC
140653 D4T
140653 NVP


Am stuck in googling i have not tried any solution

Answer Source

This would work with an on-the-fly string split approach via XML:

DECLARE @tbl TABLE(PharmacyID INT,Regimen VARCHAR(100)); 
INSERT INTO @tbl VALUES(140646,'3TC/D4T/EFV')
                      ,(140653,'ABC/D4T/NVP');

WITH Casted AS (
                SELECT PharmacyID
                      ,CAST('<x>' + REPLACE((SELECT Regimen AS [*] FOR XML PATH('')),'/','</x><x>') + '</x>' AS XML) AS TheXML
                FROM @tbl
               )
SELECT PharmacyID 
      ,a.x.value(N'(./text())[1]','nvarchar(max)') AS Regimen
FROM Casted
CROSS APPLY Casted.TheXML.nodes(N'/x') AS A(x)