Wednesday, 18 October 2017

How 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
-----------------------------------------------------------------------------------------------------





No comments:

Post a Comment

Hi User, Thank You for Visiting My Blog. Please Post Your Feedback/Comments/Query.

Subscribe to Blog Posts by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts