Wednesday, October 18, 2017

How to use XML Method to Split a Single column into Multiple columns in SQL Server

XML Method to Split a Single column into Multiple columns in SQL Server
Suppose We have a Table called Tbl_EMP with columns Emp_Id , Emp_Name where the EMP_Name have the FirstName,MiddleName and LastName combined by the delimiter "_" underscore as follows..
Select*From Tbl_EMP;
GO
Emp_ID    Emp_Name
1               ABC_CDE_DE
2               FGH_HI
3               JKL_LMN_NOP
4               PQR_RS

Now if we wants to Split this Name into 3 Columns as First_Name,Middle_Name and Last_Name , we can do it as follows using the XML Method :
DECLARE @Delimiter VARCHAR(50)
SET @Delimiter='_';
   -- <=== Here, you can change the delimiter.
WITH CTE AS
(
    SELECT
        [Emp_ID],
        [Emp_Name],
        CAST('<D>' + REPLACE([Emp_Name], @Delimiter , '</D><D>') + '</D>' AS XML)
        AS [Emp_Name XML]

    FROM  Tbl_EMP
)
SELECT
     [Emp_ID],
     [Emp_Name],
     [Emp_Name XML].value('/D[1]', 'varchar(50)') As [First_Name],
  [Emp_Name XML].value('/D[2]', 'varchar(50)') As [Middle_Name],
     [Emp_Name XML].value('/D[3]', 'varchar(50)') As [Last_Name]
FROM CTE

GO
-----------------------------------------------------
Output :
-----------------------------------------------------
If you observe the above Output, which may not looks accurate for records 2 and 4, as they don't have the Middle Name which should be Last Name.
We can handle this Using the Case Statement in above query as follows :
DECLARE @Delimiter VARCHAR(50)
SET @Delimiter='_';
   -- <=== Here, you can change the delimiter.
WITH CTE AS
(
    SELECT
        [Emp_ID],
        [Emp_Name],
        CAST('<D>' + REPLACE([Emp_Name], @Delimiter , '</D><D>') + '</D>' AS XML)
        AS [Emp_Name XML]
    FROM  Tbl_EMP )SELECT
     [Emp_ID],
     [Emp_Name],
     [Emp_Name XML].value('/D[1]', 'varchar(50)') As [First_Name],
  CASE
       WHEN ([Emp_Name XML].value('/D[2]', 'varchar(50)') IS NOT NULL)
                     AND ([Emp_Name XML].value('/D[3]', 'varchar(50)') IS NULL)
       THEN NULL
                    ELSE [Emp_Name XML].value('/D[2]', 'varchar(50)')
  END AS [Middle_Name],
  CASE
         WHEN ([Emp_Name XML].value('/D[3]', 'varchar(50)')) IS NULL
         THEN [Emp_Name XML].value('/D[2]', 'varchar(50)')
                   ELSE [Emp_Name XML].value('/D[3]', 'varchar(50)')
  END AS [Last_Name]
FROM CTE

GO

-----------------------------------------------------
Output :


-----------------------------------------------------
Notes:
--In the above query, for the XML Tags <D>;</D>, you can use any other letter like <T>;</T>.

--[Emp_Name XML].value('/D[1]' is the First Part of String with Delimiter...
--You can use the delimiter based on your source data.
-----------------------------------------------------
You can change/update the delimiter in the Source Table Column as follows :
Update Tbl_EMP
Set Emp_Name = REPLACE(Emp_Name, '_', '&')

Output :
Emp_ID Emp_Name
1            ABC&CDE&DE
2            FGH&HI
3           JKL&LMN&NOP
4            PQR&RS

--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------

No comments:

Post a Comment

Hi User, Thank You for visiting My Blog. Please post your genuine Feedback or comments only related to this Blog Posts. Please do not post any Spam comments or Advertising kind of comments which will be Ignored.

Featured Post from this Blog

How to compare Current Snapshot Data with Previous Snapshot in Power BI

How to Dynamically compare two Snapshots Data in Power BI Scenario: Suppose, we have a sample Sales data, which is stored with Monthly Snaps...

Popular Posts from this Blog