sql server - SQL query date into new columns with unique ID

Question

I have the following columns, a 5 digit ID, date and a value. The ID repeats only when a new date is present.

ID        Date     Value
11111  2014-12-31  45 
22222  2014-12-31  435 
33333  2014-12-31  11 
11111  2014-12-30  5 
22222  2014-12-30  2245 
33333  2014-12-30  86 
11111  2014-12-29  43 
22222  2014-12-29  4678 
33333  2014-12-29  2494  

I am trying to create an SQL query that will display the following (dates are column names):

ID      2014-12-31     2014-12-30       2014-12-29 
11111      45               5               43 
22222     435              2245            4678 
33333      11               86             2494 

What is the best way of doing this using MS SQL.

Thanks

Answer 1

As pointed out by the comments, you need to PIVOT your data. Here is one way using a Dynamic Crosstab.

Read this article by Jeff Moden for reference: http://www.sqlservercentral.com/articles/Crosstab/65048

CREATE TABLE temp(
    ID      INT,
    [Date]  DATE,
    Value   INT
)
INSERT INTO temp VALUES
(11111, '2014-12-31', 45),
(22222, '2014-12-31', 435),
(33333, '2014-12-31', 11),
(11111, '2014-12-30', 5),
(22222, '2014-12-30', 2245),
(33333, '2014-12-30', 86),
(11111, '2014-12-29', 43),
(22222, '2014-12-29', 4678),
(33333, '2014-12-29', 2494);

DECLARE @sql1 VARCHAR(2000) = ''
DECLARE @sql2 VARCHAR(2000) = ''
DECLARE @sql3 VARCHAR(2000) = ''

SELECT @sql1 = 
'SELECT
    ID
'

SELECT @sql2 = @sql2 + 
'   ,MIN(CASE WHEN [Date] = CAST(''' + CONVERT(VARCHAR(10), [Date], 120) + ''' AS Date) THEN Value END) AS [' 
    + CONVERT(VARCHAR(10), [Date], 120) + ']'+ CHAR(10)
FROM(
    SELECT  DISTINCT [Date] FROM temp
)t
ORDER BY [Date] DESC

SELECT @sql3 = 
    'FROM temp
    GROUP BY ID
    ORDER BY ID'

PRINT (@sql1 + @sql2 + @sql3)
EXEC (@sql1 + @sql2 + @sql3)

DROP TABLE temp

EDIT: If you want to use fixed name, you'll want to assign a number for each Date. This can be done using ROW_NUMBER():

SELECT @sql2 = @sql2 + 
'   ,MIN(CASE WHEN [Date] = CAST(''' + CONVERT(VARCHAR(10), [Date], 120) + ''' AS Date) THEN Value END) AS [Date' + CONVERT(VARCHAR, rn) + ']'+ CHAR(10)
FROM(
    SELECT 
        [Date],
        rn = ROW_NUMBER() OVER(ORDER BY [Date])
    FROM (
        SELECT  DISTINCT [Date]FROM temp
    )x
)t
ORDER BY [Date] DESC