'How can i sql pivot column [duplicate]

I've seen a lot of questions that ask about pivot tables.Even if they don't know that they are asking about pivot tables, ......

  • What is pivot?
  • How do I pivot?
  • how can i pivot this data?

+---------+----------+----------+
| Subject | Semester | Attendee |
+---------+----------+----------+
| ITB001  |        1 | John     |
| ITB001  |        1 | Bob      |
| ITB001  |        1 | Mickey   |
| ITB001  |        2 | Jenny    |
| ITB001  |        2 | James    |
| MKB114  |        1 | John     |
| MKB114  |        1 | Erica    |
+---------+----------+----------+

To Like this

+----------+----------+----------------+
| Attendee | Semester | Subject        |
+----------+----------+----------------+
| John     |        1 | ITB001,MKB114  |
| Bob      |        1 | ITB001         |
+----------+----------+----------------+
sqlsql-serverstored-procedurespivot-table


Solution 1:[1]

This isn't a pivot at all, it's string aggregation.

In SQL Server 2017+ or Azure SQL Database:

SELECT Attendee, Semester, Subjects = STRING_AGG(Subject, ',')
  FROM dbo.SomeTableName
  GROUP BY Attendee, Semester;

On older and unsupported versions, it's a heck of a lot uglier, not to mention inefficient:

SELECT ost.Attendee, ost.Semester, Subjects = STUFF((
  SELECT ',' + ist.Subject
    FROM dbo.SomeTableName AS ist
    WHERE ist.Attendee = ost.Attendee
      AND ist.Semester = ost.Semester
    FOR XML PATH(''), 
    TYPE).value(N'./text()[1]', N'varchar(max)'), 1, 1, '')
FROM dbo.SomeTableName AS ost
GROUP BY ost.Attendee, ost.Semester;

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1

Related Questions