逗号分隔字符串到表[重复]

This question already has an answer here:

这个问题在这里已有答案:

Turning a Comma Separated string into individual rows 12 answers

将逗号分隔的字符串转换为单独的行12个答案

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.

有人能帮我吗?不使用游标。

3 个解决方案

#1

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

#2

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
                           )

#3

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 .

为了您的快速参考,我包括sql小提琴。

SQLFiddle

#1