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.
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
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'
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'
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
Using below query we can split a comma separated list into a table.
SELECT StudentId 'Student Id', LTRIM(RTRIM(m.n.value('.','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)
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.