[MS SQL 2005]Ways to turn rows into columns

We are creating a web app for doing teacher gradebooks.

Each student appears in the gradebook for a class once - and has many columns. And to make matters worse each gradebook has a variable number of columns.

The data for each cell of the grid is stored in it's own row - like this:

Code:

---

StuId Column Score
  1      1    99
  1      2    98
  1      4    97
  2      1    88
  2      3    87
  3      1    50
  3      2    51
  4      4    100

---

What I need to give the web page is a recordset that looks like this

Code:

---

StuId Col1 Col2 Col3 Col4 Col5 Col6 ...
  1    99  98        97
  2    88        87
  3    50  51
  4                  100

---

Any suggestions on how to accomplish this would be appreciated!

I've never done this but I think PIVOT and UNPIVOT functions are there for similar purpose. Didi u have a look at them?

From what I can see PIVOT does an AGGREGATE function - which I don't need.

And of course I have the issue of a "variable" number of columns for each gradebook.

This would give you the results you are expecting:


SELECT * FROM StudentScore
PIVOT (MAX(Score) FOR [Column] IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10])) PT

I'm leaning towards using that method.

But to be even less elegant...

Having 5 sets of temporary tables to PIVOT into - one with 20 columns, one with 40, 60, 80 and also one with 100 columns.

Pivoting into the correct temp tables takes just 5 IF/blocks using a value of the MAX(COLUMN) from the class we are working with.

Then we select from whatever temp table we built - and our webpage is hardwired to look at 20, 40, 60, 80 and 100 column recordsets.

It loads the data into the webpage with this anyway

Code:

---

        'then the row data
        JSON = AddObjectItemWithArrayToJSON(JSON, "Values")
        For i = 0 To dtx.Rows.Count - 1
            For i2 = 1 To dtx.Columns.Count - 1
                JSON = AddArrayItemToJSON(JSON, dtx.Rows(i).Item(i2).ToString)
            Next
        Next
        JSON = EndArrayInJSON(JSON)

---