vendredi 31 juillet 2015

Pivoting a date and value column to get their combination

I have a table with this structure:

+--------+-----------+------------+------+
| userid |   date    |    item    | rank |
+--------+-----------+------------+------+
|  34444 | 01-Jul-15 | pen        |    3 |
|  34444 | 04-Jul-15 | TV         |    2 |
|  34444 | 09-Jul-15 | controller |    1 |
|    531 | 03-Jul-15 | keyboard   |    3 |
|    531 | 06-Jul-15 | pen        |    2 |
|    531 | 10-Jul-15 | bowl       |    1 |
+--------+-----------+------------+------+

Each item has already been ranked based on their dates with a limit of 3 items per user. I have their last 3 items and the dates associated with them. The items can be anything.

I want to produce a view in a way that pivots the date and item combination. For example, the desired view for this table is:

+--------+------------+-----------+-------+-----------+----------+-----------+
| userid |   item1    |   date1   | item2 |   date2   |  item3   |   date3   |
+--------+------------+-----------+-------+-----------+----------+-----------+
|  34444 | controller | 09-Jul-15 | TV    | 04-Jul-15 | pen      | 01-Jul-15 |
|    531 | bowl       | 10-Jul-15 | pen   | 06-Jul-15 | keyboard | 03-Jul-15 |
+--------+------------+-----------+-------+-----------+----------+-----------+

Is this possible?

Thanks

Aucun commentaire:

Enregistrer un commentaire