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.

Which database are you using? – Bohemian Feb 02 '12 at 22:02 — Bohemian, Feb 02 '12 at 22:02

score 2 · Answer 1 · edited Jan 18 '21 at 12:30

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;

edited Jan 18 '21 at 12:30

Community

1
1

answered Feb 02 '12 at 22:05

Conrad Frix

51,984
12
96
155

Sorry, I forgot to mention that the range of the Points is unknown at compile time, so it's not just from 1 to 3 – wd113 Feb 02 '12 at 22:09
Well you'll need to do the above but with dynamic SQL see [T-SQL Pivot? Possibility of creating table columns from row values](http://stackoverflow.com/a/2922815/119477) – Conrad Frix Feb 02 '12 at 22:16
Thanks for your replay, I tried your SQL, and get the User and Year columns right, but other columns show NULL. – wd113 Feb 02 '12 at 22:37
It worked in the [data.se example](http://data.stackexchange.com/stackoverflow/query/60706/). You'd probably need to share your sql. – Conrad Frix Feb 02 '12 at 22:45
it seems to be the data that cause the problem, I used: ('J',1988, 1988, 10), ('W', 1989, 1989, 10), ('W', 1989, 1990, 20), ('W', 1989, 1991, 30), ('W', 1989, 1992, 40) – wd113 Feb 02 '12 at 23:32

score 0 · Answer 2 · answered Feb 04 '12 at 14:55

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

sql table pivot or transform

2 Answers2