Shark-Neil Felyx Shark-Neil Felyx - 2 months ago 11
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


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

DECLARE @tbl TABLE(PharmacyID INT,Regimen VARCHAR(100)); 

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)