separate multiple values for the same column

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

Hi all,
let's say I have a table with 7 columns and one column has multiple values separated by comma (,) like this:

SQL

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:

SQL

('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

Posted 21-Apr-15 4:30am

Samira Radwan

Add a Solution

Comments

Richard Deeming 21-Apr-15 10:58am

That's an extremely poor table design, and your query is a perfect example of why it's so bad.

Samira Radwan 21-Apr-15 11:18am

yes, i know! but i didn't design it, unfortunately!

2 solutions

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Accepted Answer · 2015-04-21T04:55:00

First you need a function to split that column - there are loads out there but I like this one from sqlservercentral.com[^]

SQL

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 
END

I 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[^])

SQL

SELECT a.student, a.grad, b.splitdata
INTO table2
FROM table1 a
CROSS APPLY dbo.fnSplitString(a.courses,',') AS b

Contents of table2 are

student grad    course
Samira	grade1	maths
Samira	grade1	history
Samira	grade1	English
Samira	grade1	science
George	grade2	maths
George	grade2	german
George	grade2	geography

Maciej Los · Accepted Answer · 2015-04-21T08:12:00

Using CTE[^]:

SQL

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

SQL

SELECT *
FROM CTE

with

SQL

INSERT INTO NewTableName (student, grad, course)
SELECT student, grad, course
FROM CTE

Good luck!

separate multiple values for the same column

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

separate multiple values for the same column

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us