How can i sql pivot column

Question

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         |
+----------+----------+----------------+

and , that's an odd tag combo. Are you using MS SQL Server or MySQL? — jarlh, Apr 07 '22 at 11:27
This doesn't look like pivoting, and looks more like string aggregation. — Thom A, Apr 07 '22 at 11:29
What version of SQL Server? Please use an appropriate version-specific tag. — Aaron Bertrand, Apr 07 '22 at 11:29
If, however, you want to find out what a pivot is, and how to pivot, a simple search would give you a *wealth* of resources. — Thom A, Apr 07 '22 at 11:30
Also I don't know that I like the duplicate, most of the answers don't seem to address, well, duplicates. — Aaron Bertrand, Apr 07 '22 at 13:54

Aaron Bertrand · Accepted Answer · 2022-04-07T13:54:33.180

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;

Example db<>fiddle

How can i sql pivot column

1 Answers1