[MS SQL 2005]Ways to turn rows into columns

[szlamany]

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:

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

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!

[Pradeep1210]

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

[szlamany]

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.

[Pradeep1210]

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

[szlamany]

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

        '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)