T-sql putting values of column into one row

Question

Suppose I have the following table

ClassID    StudentID
1          10
1          11
2          12
2          10
3          13

I want to have table like this

ClassID  StudentID1   StudenID2 ...  StudentID32
1        10           11             null
2        12           10             null
3        3            null           null

Is there a way to query above table. Constraint: there could be maximum of 32 (it can be any predefined number) students for each class. I guess while loop can be used somehow, but there should be other ways.

EDIT I've tried to use Pivot, but there I've to use some kind of aggregates, but for above question I don't have to use any kind of aggregate. And in pivot I have to name the column (something like in(col1, col2,...)), but here I don't even know ids of students. Is there any general way to solve above simple question so that each row represents a unique ClassID with all students, where each StudentID is in different column (columns can be named anything you like), subject to each ClassID can have max of 32 StudentIDs (I'm not supposed to use self joins because it is not practical)

Answer 1

You need to use a combination of Count(), GROUP BY, temp tables and PIVOT function.

I have the following for the example above and it seems to work. You can modify the Pivot to derive the column names dynamically, but this requires a bit of thought.

WITH CTE
     AS (SELECT ClassID,
                COUNT(StudentID) AS StudentNum
         FROM KamTest.dbo.Table1
         GROUP BY ClassID)
     SELECT A.ClassID,
            B.StudentID,
            A.StudentNum INTO #temp
     FROM CTE AS A
     INNER JOIN
     KamTest.dbo.Table1 AS B
     ON A.ClassID=B.ClassID;

SELECT ClassID,
       MAX(StudentID),
       COUNT(StudentID)
FROM KamTest.dbo.Table1
GROUP BY ClassID;

SELECT ClassID,
       [10] AS StudentID10,
       [11] AS StudentID11,
       [12] AS StudentID12,
       [13] AS StudentID13
FROM(
    SELECT ClassID,
           StudentID,
           StudentNum
    FROM #temp) AS SourceTable PIVOT(MAX(StudentNum) FOR StudentID IN([10],
                                                                      [11],
                                                                      [12],
                                                                      [13])) AS PivotTable;

Answer 2

With a known limit that is relatively small (e.g. 10), a self join would work.