SQL Server - Possible Pivot Solution?

Question

I have a simple enough issue that has been surprisingly difficult to locate online. Perhaps I am searching on improper keywords so I wanted to stop in and ask you guys because your site has been a blessing with my studies. See below scenario:

Select student, count(*) as Total, (the unknown variable: book1, book2, book3, book4, ect...) from mystudies.

Essentially all I would like to do is list out all books for a unique student id that matches the Total count. Could someone point me in the right direction, a good read or anything, so I can get a step going in the correct direction? I am assuming it would be done via a left join (not sure how to do the x1, x2, x3 part) and then just link the two by the unique student id number (no duplicates) but everyone online points to pivot but pivot appears to put all the rows into columns instead of one single column. SQL server 2005 is the platform of choice.

Thanks!

Sorry

The following query produces my unique id (the student) and the student's count for all duplicate entries in the table:

select student, count(*) as Total 
from mystudies
group by student order by total desc

the part I don't know is how to create the left join on the table unique id (boookid)

select mystudies1.student, mystudies1.total, mystudies2.bookid 
from  (  select student, count(*) as Total 
         from mystudies
         group by student
      ) mystudies1
      left join 
      (  select student, bookid 
         from mystudies
      ) mystudies2 
         on mystudies1.student=mystudies2.student
order by mystudies1.total desc, mystudies1.student asc

Obviously the above row will produce results similar to the following:

Student    Total  BookID
000001    3        100001
000001    3        100002
000001    3        100003
000002    2        200001
000002    2        200002
000003    1        300001

But what I actually want is something similar to the following:

Student    Total  BookID
000001     3      100001, 100002, 100003
000002     2      200001, 200002
000003     1      300001

I assumed it had to be done in a left join so that it didn't alter the actual count being performed on the student. thanks!

Answer 1

In SQL-Server use the FOR XML Path Method:

SELECT  Student,
        Total,
        STUFF(( SELECT  ', ' + BookID
                FROM    MyStudies books
                WHERE   Books.Student = MyStudies.Student
                FOR XML PATH(''), TYPE
                ).value('.', 'VARCHAR(MAX)'), 1, 2, '') AS Books
FROM    (   SELECT  Student, COUNT(*) AS Total
            FROM    myStudies
            GROUP BY Student
        ) MyStudies

I have previously given a full explanation of how the XML PATH Method works here. With a further improvement to my answer pointed out here

SQL Server Fiddle

---

In MySQL AND SQLite you can use the GROUP_CONCAT function:

SELECT  Student, 
        COUNT(*) AS Total, 
        GROUP_CONCAT(BookID) AS Books
FROM    myStudies
GROUP BY Student

MySQL Fiddle

SQLite Fiddle

---

In Postgresql you can use the ARRAY_AGG Function:

SELECT  Student, 
        COUNT(*) AS Total, 
        ARRAY_AGG(BookID) AS Books
FROM    myStudies
GROUP BY Student

Postgresql Fiddle

---

In oracle you can use the LISTAGG Function

SELECT  Student, 
        COUNT(*) AS Total, 
        LISTAGG(BookID, ', ') WITHIN GROUP (ORDER BY BookID) AS Books
FROM    myStudies
GROUP BY Student

Oracle SQL Fiddle

---