-1

I have a table, the ID is not unique per a single row. each ID can have up to three rows max.

      ID      |   Name         |     Quantity
----------------------------------------------
    1         |   apple        |       1  
    1         |   orange       |      NULL  
    1         |   mango        |       3  
    2         |   banana       |       5  
    2         |   strawberries |       2  
    3         |   kiwi         |      NULL   
    3         |   lime         |       3   
    3         |   grape        |       7

I want a query that gets this result table: 

|       ID     |    Fruit 1     |   Fruit 1 Amount   |    Fruit 2     |   Fruit 2 Amount    |    Fruit 3     |   Fruit 3 Amount      
------------------------------------------------------------------------------------------------------------------------------------
|       1      |    apple       |         1          |     orange     |   NULL              |    mango       |        3
|       2      |    banana      |         5          |  strawberries  |   2                 |    NULL        |        NULL
|       3      |    kiwi        |       NULL         |     lime       |   3                 |    grape       |        7

I want a row for each ID

mdt
  • 139
  • 3
  • 17
  • Possible duplicate of [Efficiently convert rows to columns in sql server](http://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server) – Tab Alleman Jan 09 '17 at 16:27
  • @TabAlleman the result table in the question mentioned is not done per ID, I want a row for each ID – mdt Jan 09 '17 at 16:29
  • You can refer to this question http://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server – Shaminder Singh Jan 09 '17 at 16:32
  • @ShaminderSAujla I tried that, but that gives me a very different result from what I am looking for. I want the result above and I couldn't achieve it with both questions mentioned. – mdt Jan 09 '17 at 16:38
  • Does this table have a unigue row id? – P.Salmon Jan 09 '17 at 16:46
  • @P.Salmon if you mean the first table, the ID is not unique per row. each ID can have up to three rows. – mdt Jan 09 '17 at 16:48
  • In that case it is not possible to maintain the order of fruits in your output based on the data you have - would a solution which returns the fruits in alphabetic order be of use? – P.Salmon Jan 09 '17 at 16:52
  • @P.Salmon yes, it wouldn't be a problem if the fruits are in alphabetic order – mdt Jan 09 '17 at 16:56

1 Answers1

2

The subquery s uses the allocates a row number which is conditionally aggregated in the outer query. For something as simple as this I wouldn't bother with the pivot function.

1> select s.id,
2> max(case when s.rn = 1 then s.name end) as f1name,
3> max(case when s.rn = 1 then s.quantity end) as f1qty,
4> max(case when s.rn = 2 then s.name end) as f2name,
5> max(case when s.rn = 2 then s.quantity end) as f2qty,
6> max(case when s.rn = 3 then s.name end) as f3name,
7> max(case when s.rn = 4 then s.quantity end) as f3qty
8> from
9> (
10> select * ,
11> row_number() over(partition by id order by id,name) rn
12> from t
13> ) s
14> group by s.id
15> go
id          f1name          f1qty       f2name          f2qty       f3name          f3qty
----------- --------------- ----------- --------------- ----------- --------------- -----------
          1 apple                     1 mango                     3 orange                 NULL
          2 banana                    5 strawberries              2 NULL                   NULL
          3 grape                     7 kiwi                   NULL lime                   NULL

(3 rows affected)
P.Salmon
  • 17,104
  • 2
  • 12
  • 19