SQL Pivot Function for 2 Column Table-CodePudding

I have a file with id numbers and timestamps. Each id can have multiple timestamps, in addition to duplicate timestamps. I need to create a SQL pivot table out of these rows so one id is attached to multiple distinct timestamp columns. I have tried tutorials online and nothing is working so figured I would ask here. Sample dataset is below:

id          timestamp
17530018    5/23/12 12:41
20119410    5/28/12 2:40
20119410    5/28/12 2:40
20150453    6/6/12 20:52
20150453    6/6/12 20:52
20150453    6/6/12 19:45
20150453    6/6/12 19:45
20267495    6/22/12 19:50
20267495    6/22/12 19:50
20345259    6/30/12 19:15
20345259    6/30/12 19:15
20365779    7/5/12 1:29
20469785    7/30/12 10:19
20469785    7/30/12 10:19
17602687    7/30/12 15:29
20598000    8/9/12 20:45
20598000    8/9/12 20:45
20598000    8/9/12 22:37
20598000    8/9/12 22:37
20598000    8/10/12 20:40
20598000    8/10/12 20:40
20598000    8/12/12 0:51
20598000    8/12/12 0:51
20598000    8/12/12 1:00
20598000    8/12/12 1:00
20517115    8/14/12 1:34
20583314    8/14/12 1:33
20517115    8/14/12 1:34
20583314    8/14/12 1:33

Thank you in advance for any feedback/input/help.

CodePudding user response：

with data as (
    select *, row_number() over (partition by id order by "timestamp") as rn
    from T
)
select id,
    [1] as timestamp1, [2] as timestamp2, [3] as timestamp3,
    [4] as timestamp4, [5] as timestamp5, [6] as timestamp6,
    [7] as timestamp7, [8] as timestamp8, [9] as timestamp9,
    [10] as timestamp10
from data pivot (
    min("timestamp")
    for rn in ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10])
) pvt;

This will only cover up to 10. The number of columns won't be dynamic.

CodePudding user response：

I am not sure if you are trying to say that you are using MS SQL Server and after pivoting this data like:

Id, ts1, ts2 ...

It would require dynamic SQL and at seconds level end result might have too many columns with few data in cells (then you could try doing that maybe at date level):

DECLARE @times NVARCHAR(MAX) = '';
DECLARE @SQL NVARCHAR(MAX);

SELECT  @times = @times   QUOTENAME(convert(VARCHAR,[timestamp],120))   ', '
FROM    myTable
GROUP BY convert(VARCHAR,[timestamp],120);

SET @times = LEFT(@times, LEN(@times) - 1);

SET @SQL = 'SELECT *
FROM
(
        SELECT  1 AS x, id, convert(VARCHAR,[timestamp],120) as [timestamp]
        FROM myTable     
) AS Tmp
    PIVOT(sum(x) FOR [timestamp] IN (' @times ')) AS PVT
    ORDER BY 1;';

EXEC(@SQL);

Here is DBFiddle demo.