SQL Tips – Comma separated list

Today in an interview interviewer asked me to make a query to create a comma separated list which I did easily using STUFF and XML Path. Later I realized I could do it through other ways also. I will write in this blog all possible ways I know.
Table Schema

CREATE TABLE Student(StudentId int,SubjectName varchar(100))

INSERT INTO Student VALUES(111,'English')
INSERT INTO Student VALUES(111,'Science')
INSERT INTO Student VALUES(112,'English')
INSERT INTO Student VALUES(112,'Science')
INSERT INTO Student VALUES(111,'Maths')

Query 1: Below query will pull list for each StudentId  one at a time.

SELECT STUFF((SELECT ',' + SubjectName
FROM Student SS
WHERE SS.StudentId = S.StudentId
ORDER BY SubjectName
FOR XML PATH('')), 1, 1, '') AS SubjectList
FROM Student S
GROUP BY S.StudentId

output1

Query 2: Below query will return all the subjects in one string value. Basically, in this code we are checking if the variable has been initialized yet. If it hasn’t, set it to the empty string, and append the first SubjectName (no leading comma). If it has, then append a comma, then append the SubjectName.

DECLARE @listStr VARCHAR(MAX)
SELECT @listStr = COALESCE(@listStr+',' , '') + SubjectName
FROM Student
SELECT @listStr 'Subject List'

output2

Query 3: Below query will also return the same output as above query, but its implementation is different. This is also a good way but not the best way.

DECLARE @listStr VARCHAR(MAX)
SET @listStr = ''
SELECT @listStr = @listStr + SubjectName + ','
FROM Student
SELECT SUBSTRING(@listStr , 1, LEN(@listStr)-1) 'Student List'

output2

We can also use cursors to get same results but they aren’t the efficient way.

Splitting comma separated list

Suppose we have another scenario where we have a string of comma separated list and we have to return a table of it.

DECLARE @t TABLE
(
StudentID INT,
Subjects VARCHAR(8000)
)

INSERT INTO @T
SELECT  
        StudentId,
        STUFF((SELECT  ',' + SubjectName
            FROM Student SS
            WHERE  SS.StudentId = S.StudentId
            ORDER BY SubjectName
        FOR XML PATH('')), 1, 1, '') AS 'Subject List'
FROM Student S
GROUP BY S.StudentId

output3

Using below query we can split a comma separated list into a table.

Query

SELECT StudentId 'Student Id',
LTRIM(RTRIM(m.n.value('.[1]','varchar(8000)'))) AS 'Subject Name'
FROM
(
SELECT 
	StudentID,
	CAST('' + REPLACE(Subjects,',','') + '' AS XML) AS node
FROM @t
)XmlTable
CROSS APPLY node.nodes('/XMLRoot/RowData')m(n)

output4

We can use above query in UDF’s also to return a table value as output.

I hope it will help someone. Any comments or suggests are welcome.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s