In SQL Server we don't find the Split function based on the delimiter . By using logic script or by creating user defined function we can achieve this functionality .
from the above Source we have to display like below
Create two Table variables (@table1,@table2)
DECLARE @TABLE1 TABLE
(
ROW INT IDENTITY(1,1),
[PERSONID] INT,
[PERSONNAME] NVARCHAR(50) NULL,
[PERSONSKILLID'S] NVARCHAR(50) NOT NULL
);
DECLARE @TABLE2 TABLE
(
[PERSONID] INT,
[PERSONNAME] NVARCHAR(50) NULL,
[PERSONSKILLID] NVARCHAR(50) NOT NULL
);
DECLARE @ROWCOUNT INT,@INCR INT
SET @INCR=1
INSERT INTO @TABLE1([PERSONID] ,[PERSONNAME],[PERSONSKILLID'S])
SELECT [PERSONID] ,[PERSONNAME],[PERSONSKILLID'S] FROM [DBO].[SPLIT_PERSON]
SET @ROWCOUNT =(SELECT COUNT(*) FROM @TABLE1)
WHILE @INCR<=@ROWCOUNT
BEGIN
DECLARE @CHARINDEX INT =(SELECT CHARINDEX(',',[PERSONSKILLID'S]) FROM @TABLE1 WHERE ROW=@INCR)
WHILE @CHARINDEX>0
BEGIN
INSERT INTO @TABLE2([PERSONID]
,[PERSONNAME]
,[PERSONSKILLID])
SELECT [PERSONID],[PERSONNAME], CASE WHEN CHARINDEX(',',[PERSONSKILLID'S])>0 THEN SUBSTRING([PERSONSKILLID'S],0, CHARINDEX(',',[PERSONSKILLID'S])) ELSE [PERSONSKILLID'S] END AS [PERSONSKILLID'S] FROM @TABLE1 T
WHERE T.ROW=@INCR
UPDATE @TABLE1 SET [PERSONSKILLID'S]=SUBSTRING([PERSONSKILLID'S],CHARINDEX(',',[PERSONSKILLID'S])+1,LEN([PERSONSKILLID'S])) WHERE ROW=@INCR
SET @CHARINDEX=(SELECT CHARINDEX(',',[PERSONSKILLID'S]) FROM @TABLE1 WHERE ROW=@INCR)
END
IF @CHARINDEX=0
BEGIN
INSERT INTO @TABLE2([PERSONID]
,[PERSONNAME],[PERSONSKILLID])
SELECT [PERSONID]
,[PERSONNAME], CASE WHEN CHARINDEX(',',[PERSONSKILLID'S])>0 THEN SUBSTRING([PERSONSKILLID'S],0, CHARINDEX(',',[PERSONSKILLID'S])) ELSE [PERSONSKILLID'S] END AS [PERSONSKILLID'S] FROM @TABLE1 T
WHERE T.ROW=@INCR
END
SET @INCR=@INCR+1
END
SELECT * FROM @TABLE2 CREATE FUNCTION DBO.SPLIT_STRING(@STRING VARCHAR(8000), @DELIMITER CHAR(1))
RETURNS @RETURNTABLE TABLE (ITEMS VARCHAR(8000))
AS
BEGIN
DECLARE @IDX INT
DECLARE @SLICE VARCHAR(8000)
SELECT @IDX = 1
IF LEN(@STRING)<1 OR @STRING IS NULL RETURN
WHILE @IDX!= 0
BEGIN
SET @IDX = CHARINDEX(@DELIMITER,@STRING)
IF @IDX!=0
SET @SLICE = LEFT(@STRING,@IDX - 1)
ELSE
SET @SLICE = @STRING
IF(LEN(@SLICE)>0)
INSERT INTO @RETURNTABLE(ITEMS) VALUES(@SLICE)
SET @STRING = RIGHT(@STRING,LEN(@STRING) - @IDX)
IF LEN(@STRING) = 0 BREAK
END
RETURN
END
DECLARE @TABLE1 TABLE
(
ROW INT IDENTITY(1,1),
[PERSONID] INT,
[PERSONNAME] NVARCHAR(50) NULL,
[PERSONSKILLID'S] NVARCHAR(50) NOT NULL
);
INSERT INTO @TABLE1([PERSONID] ,[PERSONNAME],[PERSONSKILLID'S])
SELECT [PERSONID] ,[PERSONNAME],[PERSONSKILLID'S] FROM [DBO].[SPLIT_PERSON]
DECLARE @X VARCHAR(50)
DECLARE @ROWCOUNT INT,@INCRE INT
DECLARE @TAB TABLE (ROW INT,NAME VARCHAR(50),[PERSONSKILLID] VARCHAR(10))
SET @ROWCOUNT=(SELECT COUNT(*) FROM @TABLE1)
SET @INCRE=1
WHILE @INCRE<=@ROWCOUNT
BEGIN
SET @X= (SELECT [PERSONSKILLID'S] FROM @TABLE1 WHERE ROW=@INCRE)
INSERT @TAB (ROW,[PERSONSKILLID])
SELECT @INCRE,ITEMS FROM SPLIT_STRING(@X,',')
SET @INCRE=@INCRE+1
END
--SELECT * FROM @TAB
SELECT T1.PERSONID,T1.PERSONNAME,T.PERSONSKILLID FROM @TAB T
JOIN @TABLE1 T1 ON T1.ROW=T.ROW 
Kiran Reddy A
Kiran has been a part of the IT industry for more than six years and Involved in many MSBI projects and product development with extensive usage of ETL and Reporting tools like SQL Server Integration Services (SSIS), SQL Server Analysis Services (SSAS), SQL Server Reporting Service (SSRS) and T-SQL
Get Free Email Updates to your Inbox!
