Skip to content

Instantly share code, notes, and snippets.

@marifrahman
Created August 5, 2013 03:08
Show Gist options
  • Save marifrahman/6153194 to your computer and use it in GitHub Desktop.
Save marifrahman/6153194 to your computer and use it in GitHub Desktop.
SQL to create single record from Multiple rows
Scenario :
SubjectID StudentName
---------- -------------
1 Mary
1 John
1 Sam
2 Alaina
2 Edward
To
SubjectID StudentName
---------- -------------
1 Mary, John, Sam
2 Alaina, Edward
Select distinct ST2.SubjectID,
substring((Select ','+ST1.StudentName AS [text()]
From dbo.Students ST1
Where ST1.SubjectID = ST2.SubjectID
ORDER BY ST1.SubjectID
For XML PATH ('')),2, 1000) [Students]
From dbo.Students ST2
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment