0

I have the following table in my MS SQL server:

User | Item | Date
1    | A    | 2015-03-01
1    | B    | 2015-03-03
1    | C    | 2015-03-02
1    | A    | 2015-03-05
2    | C    | 2015-03-01
2    | C    | 2015-03-03
2    | B    | 2015-03-02

And this table should be aggregated / pivoted / transposed into this:

User | 1st_item | 2nd_item | 3rd_item | 4th_item
1    | A        | C        | B        | A
2    | C        | B        | C        | NULL

(The order of the items is defined by the date in the original data.) Unfortunately I have no clue how to get there... Ideally the solution would also work for a flexible number of items (not fixed to 4 as in the example).

What I tried so far:

  • I used ROW_NUMBER() OVER (PARTITION BY user ORDER BY date) to generate a column giving me the order within each user's items
  • I tried using PIVOT - but here I have no clue how to get the items into the columns added by this command

Thanks for any help and/or hint in advance!

Daniel
  • 2,409
  • 2
  • 26
  • 42
  • 1
    can there be more than 4 items per user? – Vamsi Prabhala Jun 20 '16 at 14:58
  • There might be more then 4. But for now it would be OK to limit it to 4... – Daniel Jun 20 '16 at 15:00
  • Well, you can try doing it with the `ROW_NUMBER()` like you suggested and combine it with conditional aggregation : `SELECT t.user, MAX(CASE WHEN t.rnk = 1 THEN t.item END) as 1st_item, MAX(CASE WHEN t.rnk = 2 THEN t.item END) as 2nd_item, MAX(CASE WHEN t.rnk = 3 THEN t.item END) as 3rd_item FROM (SELECT s.*, ROW_NUMBER() OVER(PARTITION BY s.user ORDER BY s.date) as rnk FROM YourTable s) GROUP BY t.user` – sagi Jun 20 '16 at 15:03
  • sage - thank you! This does the trick! – Daniel Jun 20 '16 at 15:10

1 Answers1

0

Pivot example

SELECT  [User],
        [1] AS [1st_item],
        [2] AS [2nd_item],
        [3] AS [3rd_item],
        [4] AS [4th_item]
FROM    (SELECT [User],
                [Item],
                ROW_NUMBER() OVER (PARTITION BY [User] ORDER BY [Date]) Rn
        FROM    myTable) mt
PIVOT (
    MAX([Item])
    FOR Rn IN ([1],[2],[3],[4])
) p
JamieD77
  • 13,796
  • 1
  • 17
  • 27
  • Thank you - also this does solve my problem! When I tried it, I used AVG or SUM instead of MAX which then did not work out with a item string... – Daniel Jun 20 '16 at 15:16