为同一列分隔多个值 [英] separate multiple values for the same column
问题描述
大家好,
假设我有一个包含7列的表,一列有多个以逗号(,)分隔的值,如下所示:
table1(' Samira',' grade1',' 数学,历史,英语,science',' ',' ',' ' )...
我想要做的是重复相同的记录多次,就像这一列中的值一样,换句话说table1应该是这样的:
(' Samira',' grade1',' maths',' ',' ',' ')
(' Samira',' grade1',' history',' ', ' ',' ')
(' Samira',' grade1',' English',' ',' ',' ' )
(' Samira',' grade1',' science ',' ',' ',' ')
有没有办法在SQL中执行此操作?
i知道我可以使用 SUBSTRING ,但如何重复同样的记录?
提前致谢
Samira
首先你需要一个功能来拆分该列 - 那里有负载,但我喜欢这个来自 sqlservercentral.com [ ^ ]
创建 功能 [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 @开始 = @ end + 1
SET @ end = CHARINDEX ( @ delimiter , @ string , @ start )
END
返回
END我创建了一些看起来像这样的测试数据
create table table1
(
student varchar( 30),
grad varchar(20),
course varchar(max)
)
insert into table1 values
('Samira','grade1','maths ,历史,英语,科学'),
('乔治','等级2','数学,德语,地理')
然后我可以生成另一个使用CROSS APPLY的表格(请参阅让OUTER和CROSS APPLY为您服务 [ ^ ])SELECT a.student,a.grad,b.splitdata
INTO table2
FROM table1 a
CROSS APPLY dbo.fnSplitString(a.courses,' ,') AS b
table2的内容是学生毕业课程
Samira grade1数学
Samira grade1 history
Samira grade1 english
Samira grade1 science
George grade2 maths
George grade2 german
George grade2 geography
使用 CTE [ ^ ]:
CREATE TABLE #table1
(
student varchar ( 30 ),
grad varchar ( 20 ),
courses varchar (max)
)
INSERT INTO #table1(学生,毕业,课程)
VALUES (' Samira',' grade1',' 数学,历史,英语,科学'),
( George',' grade2',' maths,german,geography')
; WITH CTE AS
(
SELECT 1 AS LoopNo,student,grad, LEFT (课程,CHARINDEX (' ,',courses)-1) AS 当然, RIGHT (课程,LEN(课程)-CHARINDEX(' ,',课程)) AS 剩余
FROM #table1
WHERE CHARINDEX(' ,' ,课程)> 0
UNION ALL
SELECT LoopNo + 1 AS LoopNo,学生,毕业, LEFT (剩余,CHARINDEX(' ,',Remainder)-1) AS 当然, RIGHT (剩余,LEN(剩余)-CHARINDEX( ' ,',Remainder)) AS 剩余
FROM CTE
WHERE CHARINDEX(' ,,Remainder)> 0
UNION ALL
SELECT LoopNo + 1 < span class =code-keyword> AS LoopNo,student,grad,Remainder AS >当然, NULL AS 剩余
FROM CTE
WHERE CHARINDEX(' ,',剩余)= 0
)
SELECT *
FROM CTE
DROP 表# table1
结果:
LoopNo学生毕业课程剩余
1 Samira grade1数学史,英语,science
1 George grade2 maths german,geography
2 George grade2 german geography
3 George grade2 geography NULL
2 Samira grade1 history English,science
3 Samira grade1 English科学
4 Samira grade1 science NULL
要将数据添加到新表中,请替换
< pre lang =sql> S. ELECT *
FROM CTE
with
INSERT INTO NewTableName(学生,毕业,课程)
SELECT 学生,毕业,课程
FROM CTE
祝你好运!
Hi all,
let's say I have a table with 7 columns and one column has multiple values separated by comma (,) like this:
table1 ('Samira','grade1','maths,history,English,science','','','' )...
what i want to do is repeat the same record as many times as the values in this column, in other words table1 should look like this:
('Samira','grade1','maths','','','' )
('Samira','grade1','history','','','' )
('Samira','grade1','English','','','' )
('Samira','grade1','science','','','' )
is there a way to do that in SQL?
i know i could use SUBSTRING, but how to repeat the same record?
Thanks in advance
Samira
First you need a function to split that column - there are loads out there but I like this one from sqlservercentral.com[^]
CREATE 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 ENDI created some test data that looks like this
create table table1 ( student varchar(30), grad varchar(20), courses varchar(max) ) insert into table1 values ('Samira','grade1','maths,history,English,science'), ('George','grade2','maths,german,geography')
I can then generate another table using CROSS APPLY (see Making OUTER and CROSS APPLY work for you[^])SELECT a.student, a.grad, b.splitdata INTO table2 FROM table1 a CROSS APPLY dbo.fnSplitString(a.courses,',') AS b
Contents of table2 arestudent grad course Samira grade1 maths Samira grade1 history Samira grade1 English Samira grade1 science George grade2 maths George grade2 german George grade2 geography
Using CTE[^]:
CREATE TABLE #table1 ( student varchar(30), grad varchar(20), courses varchar(max) ) INSERT INTO #table1 (student, grad,courses) VALUES ('Samira','grade1','maths,history,English,science'), ('George','grade2','maths,german,geography') ;WITH CTE AS ( SELECT 1 AS LoopNo, student, grad, LEFT(courses, CHARINDEX(',', courses)-1) AS course, RIGHT(courses, LEN(courses) -CHARINDEX(',', courses)) AS Remainder FROM #table1 WHERE CHARINDEX(',', courses)>0 UNION ALL SELECT LoopNo + 1 AS LoopNo, student, grad, LEFT(Remainder, CHARINDEX(',', Remainder)-1) AS course, RIGHT(Remainder, LEN(Remainder) -CHARINDEX(',', Remainder)) AS Remainder FROM CTE WHERE CHARINDEX(',', Remainder)>0 UNION ALL SELECT LoopNo + 1 AS LoopNo, student, grad, Remainder AS course, NULL AS Remainder FROM CTE WHERE CHARINDEX(',', Remainder)=0 ) SELECT * FROM CTE DROP TABLE #table1
Result:
LoopNo student grad course Remainder 1 Samira grade1 maths history,English,science 1 George grade2 maths german,geography 2 George grade2 german geography 3 George grade2 geography NULL 2 Samira grade1 history English,science 3 Samira grade1 English science 4 Samira grade1 science NULL
To add data into new table, replace
SELECT * FROM CTE
with
INSERT INTO NewTableName (student, grad, course) SELECT student, grad, course FROM CTE
Good luck!
这篇关于为同一列分隔多个值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!