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

Maybe this will help:

First create some test data:

CREATE TABLE tblPoints ([User] VARCHAR(100), [Year] INT,Points INT,Value INT)
INSERT INTO tblPoints
SELECT 'A',1997,1,10 UNION ALL
SELECT 'A',1997,2,30 UNION ALL
SELECT 'A',1997,3,40 UNION ALL
SELECT 'A',1999,1,70 UNION ALL
SELECT 'B',1993,1,7 UNION ALL
SELECT 'B',1993,3,4 UNION ALL
SELECT 'C',2001,1,10

The concating the columns:

DECLARE @cols VARCHAR(MAX)
SELECT  @cols = COALESCE(@cols + ','+QUOTENAME('Points'+Points),
                     QUOTENAME('Points'+Points))
FROM
    (
        SELECT
            ROW_NUMBER() OVER(PARTITION BY tblPoints.Points ORDER BY tblPoints.Points) AS RowNbr,
            CAST(tblPoints.Points AS VARCHAR(5)) AS Points
        FROM 
            tblPoints
    ) AS tbl
WHERE
    tbl.RowNbr=1

Then create the dynamic sql and executing it:

DECLARE @query NVARCHAR(4000)=
N'SELECT
    *
FROM
(
    SELECT
        tblPoints.[User],
        tblPoints.[Year],
        ''Points''+CAST(tblPoints.Points AS VARCHAR(5)) AS Points,
        tblPoints.Value
    FROM
        tblPoints
) AS p
PIVOT
(
    SUM(Value) FOR Points IN('+@cols+')
) AS pvt
ORDER BY [User]'

EXECUTE(@query)

And the because I don't want the point table:

DROP TABLE tblPoints

Answer 2

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;