1

I have a scenario where I have to convert comma separated values to columns.

Actual:

ID   NAME  Col1       Col2 
 1    A    AB,BC,CD     
 2    B                XY,YZ 
 3    C    DE,FE

Expected:

ID  NAME   Col1       Col2 
 1   A      AB 
 1   A      BC 
 1   A      CD 
 2   B                 XY 
 2   B                 YZ 
 3   C      DE 
 3   C      EF

Below is the query

SELECT a.ID,
       a.Name,
       b.splitdata ,
       c.splitdata
FROM dbo.TABLE1 a CROSS APPLY dbo.fnSplitString(a.COL1,',') AS b
CROSS APPLY dbo.fnSplitString(a.COL2,',') AS c

I am getting result zero. Please let me know if anyone can help me.

Below is the split function

ALTER FUNCTION [dbo].[fnSplitString]   
(   
    @string NVARCHAR(MAX),   
    @delimiter CHAR(1)   
)   
RETURNS @output TABLE(splitdata NVARCHAR(MAX)   
)   
BEGIN   
    DECLARE @start INT, @end INT   
    SELECT @start = 1, @end = CHARINDEX(@delimiter, @string)   
    WHILE @start < LEN(@string) + 1 BEGIN   
        IF @end = 0    
            SET @end = LEN(@string) + 1  

        INSERT INTO @output (splitdata)    
        VALUES(SUBSTRING(@string, @start, @end - @start))   
        SET @start = @end + 1   
        SET @end = CHARINDEX(@delimiter, @string, @start)  

    END   
    RETURN   
END
Nebi
  • 306
  • 2
  • 10
user3485295
  • 11
  • 1
  • 3
  • 4
    Please spend some time formatting your question. – p.s.w.g Apr 01 '14 at 16:35
  • 3
    I tried to clean up your **HUGE** mess you posted - but sorry, I **really** don't know what that first part is supposed to look like. Please, next time - if you ask us to spend our time reading and answering your question, **AT LEAST** spend a little time on posting a question in a readable and clear format!! – marc_s Apr 01 '14 at 16:41
  • Thanks Marc_s for formaating . When i tried posting the question i was not allowed to post question as above . I was getting a formating issue – user3485295 Apr 01 '14 at 16:58
  • What's the source code for your `fnSplitString()` UDF? –  Apr 01 '14 at 17:00
  • make sure your function is working right. – hazimdikenli Apr 01 '14 at 17:35
  • Please tell the purpose here is to fix the broken schema? Storing comma separated data is wrong in the first place, and you're learning part of the reason why with this question. – Joel Coehoorn Apr 01 '14 at 17:38

1 Answers1

0

Most likely your function doesn't handle NULLs. 

    SELECT a.ID,
           a.Name,
           b.splitdata ,
           c.splitdata
    FROM dbo.TABLE1 a 
    CROSS APPLY dbo.fnSplitString(isnull(a.COL1,'')',') AS b
    CROSS APPLY dbo.fnSplitString(isnull(a.COL2,''),',') AS c
dean
  • 9,960
  • 2
  • 25
  • 26
  • If this answer was useful to you, please accept / upvote so the others can benefit from it. – dean Apr 03 '14 at 18:31
  • you are missing a , in the first fnSplitString function: so it should look like the second dbo.fnSplitString(isnull(a.COL1,''),',') – Nebi Sep 07 '16 at 13:42