sql table pivot or transform

Question

I have a table of data like the following

User Year  Points  Value
A    1997  1       10
A    1997  2       30
A    1997  3       40
A    1999  1       70
B    1993  1       7
B    1993  3       4
C    2001  1       10
.....

I want the table to be transformed as such:

User   Year  Points1  Points2  Points3 ....
A      1997  10       30       40
A      1999  70       null     null
B      1993  7        null     4
C      2001  10       null     null
......

The range of the Points is unknown at compile time, so it's not just from 1 to 3. It almost like making the Points as the column header in the new table. I suppose SQL PIVOT is a good option, but I haven't got any luck playing with it. I'm using SQL 2008.

Answer 1

Since you mentioned SQL PIVOT I'm guessing you're using SQL Server 2005 or later or Oracle 11g

SQL 2005

SELECT [user], year, [1] as Point1, [2] as Point2, [3] as Point3 
FROM
(

SELECT  [user], year , points, Value
    FROM table ) AS SourceTable
PIVOT
(
SUM(Value)
FOR Points IN ([1], [2], [3])
) AS PivotTable
ORDER BY [user]

see working example at this data.se query

Oracle 11g

If you're using Oracle 11g it would be somthing like this (not sure about the field alias)

SELECT *
FROM   (
        SELECT  user, year , points, Value
        FROM table )
PIVOT  (SUM(Value) AS sum_value FOR (Points ) IN ('1' as Point1 , 
                                                  '2' as Point2, 
                                                 '3' as Point3))
Order by User;