Qfyilyi

Suppose I have a table like this:

Id      Types      Timestamp



1        A          2014-02-04 00:00:00

2        A          2014-02-05 00:00:00

1        A          2014-02-05 03:59:00 

3        C          2014-05-06 03:59:00

1        B          2014-02-04 03:00:00

2        D          2014-02-05 00:40:00 

I would like the output to be like this:

Id     1       2        3          4          5           etc

1      A       B        A          C          D           ...

2      A       D        NULL       NULL       NULL

3      C       NULL     NULL       NULL       NULL

Is it possible to make time expresses the type's order.

Thanks for any hints.

Preliminary comments:

1. SQL can only return a predefined number of columns returned. IMHO, the best you can get is values concatenated in an array.


2. I have name your input table MyTable and renamed the column Timestamp to MyTimestamp to avoid conflict with the corresponding type's keyword.


3. You have put C and D in the 1 row of your output. I will treat it as a typo (they are not on ID = 1)

-

WITH RECURSIVE ConcatAndOrder(ID, MyResult, RowNumForOrder, RowCountForOrder) AS (

    SELECT ID, ARRAY[Type], RowNumForOrder, RowCountForOrder

    FROM IndexedTable

    WHERE RowNumForOrder = 1

    UNION ALL

    SELECT I.ID, MyResult || I.Type, I.RowNumForOrder, I.RowCountForOrder

    FROM IndexedTable I

    JOIN ConcatAndOrder C on I.ID = C.ID and I.RowNumForOrder = C.RowNumForOrder + 1

), IndexedTable(ID, Type, RowNumForOrder, RowCountForOrder) AS (

    SELECT ID, Type, 

        row_number() OVER (PARTITION BY ID ORDER BY MyTimestamp),

        count(*) OVER (PARTITION BY ID)

    FROM MyTable

)

SELECT ID, MyResult

FROM ConcatAndOrder

WHERE RowNumForOrder = RowCountForOrder

ORDER BY ID