I am stuck in a complicated problem. I do know that it can be solved by pivoting but have no idea how to do it.
The data looks like:
------------------------------------------------------------------
| user_id | order_id | service | head | coordinatior | contract |
------------------------------------------------------------------
| 1020 | 3 | 105 | 1 | 1 | 1 |
| 364277 | 1 | 105 | 0 | 0 | 0 |
| 364277 | 3 | 105 | 0 | 0 | 0 |
| 367277 | 2 | 3763454 | 1 | 1 | 0 |
| 1020 | 1 | 3764142 | 0 | 1 | 1 |
| 1020 | 2 | 665443 | 0 | 1 | 1 |
------------------------------------------------------------------
And this is how I'd like to have according to the order:
| user_id | service1 | head1 | coordinatior1 | contract1 | service2 | head2 | coordinatior2 | contract2 | service3 | head3 | coordinatior3 | contract3 |
--------------------------------------------------------------------------------------------------------------------------------------------------------------
| 1020 | 3764142 | 0 | 1 | 1 | 665443 | 0 | 1 | 1 | 105 | 1 | 1 | 1 |
| 364227 | 105 | 0 | 0 | 0 | 3763454 | 1 | 1 | 0 | 105 | 0 | 0 | 0 |
--------------------------------------------------------------------------------------------------------------------------------------------------------------
Right now I displayed 3 services as it will become a huge horizontal scrollbar but there can be max 10 services per user_id which mean it is possible that some users may have 4 services.
This is what I have for query.
SELECT user_id, service FROM table
PIVOT (?) FOR order_id IN (0, 1, 2, 3 ,4 ,5 ,6, 7, 8, 9, 10))
and I have no idea how to proceed forward. Any help? Thanks in advance :-)
Try this code:
select
user_id
,max(case when order_id=1 then service end) as service_1
,max(case when order_id=2 then service end) as service_2
from
table
group by user_id
service_1,2..10 is the same.