In this post, we are going to learn a technique to combine all the values of a row (based on a particular condition) with a separator, in a column along with other columns. What i meant by this let me explain it in more detail. Have a look on below question as raised by a colleague;
I have a Students table and a Subjects table. One student has been assigned to multiple Subjects in a transaction table. I want to extract the name of all the Subjects for which the student is assigned in a comma separated way along with other details of the student. If student is not assigned yet, i want to show a “Not Assigned Yet” value for those records.
To achieve this task, i have created a demo script and sharing the same with all the details. Below you can find the table structures and demo data.
We are creating three tables as Students, Subjects and StudentSubjectMappings for our demo.
--Creating Students Table CREATE TABLE dbo.Students ( StudentID INT, StudentName NVARCHAR(100), Class VARCHAR(10), ) GO --Creating Subjects Table CREATE TABLE dbo.Subjects ( SubjectID INT, SubjectName NVARCHAR(100), ) GO --Creating Student and subject mapping in a transaction table named StudentSubjectMappings CREATE TABLE dbo.StudentSubjectMappings ( StudentID INT, SubjectID INT ) GO
Insert some demo values in all these created tables as below;
INSERT INTO dbo.Students(StudentID, StudentName, Class) VALUES(1, 'Student 1', 'X'), (2, 'Student 2', 'XI'), (3, 'Student 3', 'XII'), (4, 'Student 4', 'X'), (5, 'Student 5', 'XI'), (6, 'Student 6', 'XII') GO INSERT INTO dbo.Subjects(SubjectID, SubjectName) VALUES(1, 'Physics'), (2, 'Chemistry'), (3, 'Mathematics'), (4, 'English'), (5, 'Social Science'), (6, 'Hindi') GO INSERT INTO dbo.StudentSubjectMappings(StudentID, SubjectID) VALUES(1, 1), (1, 2), (1, 3), (1, 4), (1, 5), (1, 6), (2, 1), (2, 2), (2, 3), (2, 4), (3, 5), (3, 6) GO
Now have a look on the tables data.
SELECT * FROM dbo.Students
SELECT * FROM dbo.Subjects
SELECT * FROM dbo.StudentSubjectMappings
Now to get the final output as required, use this query;
SELECT StudentID, StudentName, Class, ISNULL(STUFF((SELECT ', ' + SubjectName FROM dbo.StudentSubjectMappings SSM INNER JOIN dbo.Subjects SUB ON SUB.SubjectID = SSM.SubjectID WHERE SSM.StudentID = STD.StudentID FOR XML PATH('') ), 1, 1, ''), 'Not Assigned Yet') AS SubjectList FROM dbo.Students STD
Let’s discuss this trick:
To achieve this task, i have used “FOR XML” clause with “PATH” mode inside a sub-query. FOR XML clause is used to retrieve the result of a SQL query as XML data and “PATH” mode for the “FOR XML” clause provides a way to mix elements and attributes.
In above query, i have used this trick. I have used a blank element name for PATH mode inside PATH(”). It will cause to create a XML data with blank element name. Also nothing is specified as a column name for expression ‘, ‘ + SubjectName which is an expression for a column in our subquery. This blank column name will cause a blank child element name for the created XML. So in our XML data, we don’t have any element name and it’s just a string created from all the rows.
I have prefixed all the row values with a comma and then removed it from start which is easy to find. To remove it, i have used a STUFF function to do so. Adding the same comma at end and then removing it from end will need to find the length of the string to remove it from end. Now for each row in upper query, the sub query will execute and find all the rows in “dbo.Subject” table and then it will combine all the values in one row as described above.
##Below is an addition from comment section to highlight this important point in main post##
HTML character handling in FOR XML PATH
Pointed by : [Jeff Moden] & Answered by : [John Imel]
Thanks to @Jeff Moden for highlighting this point and @John Imel for answering this nicely in comment section of this post. It’s really appreciable and helpful. Hats off to both of you!
From comment section i am here adding a provision to handle html characters in above code. I am leaving the above code untouched, hence in case you have to use the output of above query in an html page where html encoded values will be decoded back, it will work fine. But in case you have to display it as it is somewhere, it will encode the html characters like;
& sign to @amp; and < sign to < and > sign to > etc.
To avoid such kind of encoding use this code;
SELECT StudentID, StudentName, Class, ISNULL(STUFF((SELECT ', ' + COALESCE(SubjectName, '') FROM dbo.StudentSubjectMappings SSM INNER JOIN dbo.Subjects SUB ON SUB.SubjectID = SSM.SubjectID WHERE SSM.StudentID = STD.StudentID FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') , 1, 1, ''), 'Not Assigned Yet') AS SubjectList FROM dbo.Students STD
Here, TYPE directive forces the data type of this FOR XML query as xml data type on which we can apply xml type methods. In this query, value method has been applied with ‘.’ as XQuery expression and NVARCHAR(MAX) as SQLType.
##Addition ends here##
A Question For You:
Are you interested to answer a simple question asked below?
Q : What will be the output of the below query:
SELECT LEN('ABC ')
Length of ABC string followed by 3 blank spaces using LEN function in SQL Server.
It may be a silly question for you but trust me, it can help some others to explore the way LEN function works.
I request you to try it without executing it in SQL and put your answer in comment section honestly. You can also put your explanation to help others.
Thanks for your support and patience.