comma separated string to a table

Question

I have a table with values as follows

StudentID | Name | Subscribed Subject |
101       |John  | Maths, Bio, Zoo    |
102       |Mary  | Bio, Zoo           |

I want to retrieve the information as follows

StudentID | Name | Subscribed Subject |
101       |John  | Maths              |
101       |John  | Bio                |
101       |John  | Zoo                |
102       |Mary  | Bio                |
102       |Mary  | Zoo                |

Can some one help me? Without using cursor.

Don't ever store relational data like that. Use a [junction table](https://en.wikipedia.org/wiki/Junction_table) — Phil, Oct 06 '15 at 02:22

score 1 · Answer 1 · answered Oct 06 '15 at 06:32

Try this

Fiddle Demo

Create FUNCTION [dbo].[fn_Split](@text varchar(8000), @delimiter varchar(20))
RETURNS @Strings TABLE
(   
  position int IDENTITY PRIMARY KEY,
  value varchar(8000)  
)
AS
BEGIN

DECLARE @index int
SET @index = -1

WHILE (LEN(@text) > 0)
  BEGIN 
    SET @index = CHARINDEX(@delimiter , @text) 
    IF (@index = 0) AND (LEN(@text) > 0) 
      BEGIN  
        INSERT INTO @Strings VALUES (@text)
          BREAK 
      END 
    IF (@index > 1) 
      BEGIN  
        INSERT INTO @Strings VALUES (LEFT(@text, @index - 1))  
        SET @text = RIGHT(@text, (LEN(@text) - @index)) 
      END 
    ELSE
      SET @text = RIGHT(@text, (LEN(@text) - @index))
    END
  RETURN
END


select t.StudentID,t.Name ,test.value
from  tablename t cross apply fn_Split(t.SubscribedSubject,',') as test

score 0 · Answer 2 · answered Oct 06 '15 at 04:52

try this code

      SET @STRSQL = 'SELECT ''' + REPLACE(Subscribed_Subject, ',',
                                                ''' ,''') + ''''

            DECLARE @tbl TABLE
                (
                  col1 VARCHAR(100) 

                )

insert your data to temp table then try to select from temp.

            INSERT  INTO @tbl
                    EXECUTE ( @STRSQL
                           )

score 0 · Answer 3 · answered Oct 06 '15 at 05:25

You can achieve the functionality with below query.

SELECT A.[StudentID],  
     Split.a.value('.', 'VARCHAR(100)') AS String  
 FROM  (SELECT [StudentID],  
        CAST ('<M>' + REPLACE(Subscribed, ',', '</M><M>') + '</M>' AS XML) AS String  
        FROM  Student) AS A CROSS APPLY String.nodes ('/M') AS Split(a);

For your quick reference, I am including the sql fiddle .

SQLFiddle

comma separated string to a table

3 Answers3