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
1
Try this
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
0
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
0
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小提琴。
#1
1
Try this
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
0
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
0
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小提琴。