sql create columns from group by collection

Question

I have a table in the following form

chain   |branch
________|________|
    a   |UK
    a   |US
    b   |ISRAEL
    b   |UK
    b   |FRANCE
    b   |BELGIUM
    c   |NIGERIA

and i would like to create a new table in the following format

chain   |branch_1|branch_2|branch_3|branch_4
________|________|________|________|________|
    a   |  UK    |  US    |--------|--------|
    b   |  ISRAEL|  UK    | FRANCE |BELGIUM |
    c   | NIGERIA|--------|--------|--------|

For further clarification, imagine that you can do a group by (chain) where the aggregate function is the identity so that

group_1->(element1,element2,element3,..,elementM)
group_2->(element1,element2,element3,..,elementN)
...
group_X->(element1,element2,element3,..,elementZ)

so a new table will be created which will have R+K columns where R are the number of columns that we group by (in our case that is the column 'chain' so R=1) and K is the max count of the groups (in our case that is four, corresponding to chain 'b')

I am sure that this must be a common question, so my apologies if this been answered before, but i could not find anything.

EDIT: THIS IS NOT A PIVOT TABLE A pivot table in that case would be

chain   |UK      |US      |ISRAEL  |FRANCE  |BELGIUM |NIGERIA |
________|________|________|________|________|________|________|
____a___|____1___|____1___|____0___|____0___|____0___|____0___|
____b___|____1___|____0___|____1___|____1___|____1___|____0___|
____c___|____0___|____0___|____0___|____0___|____0___|____1___|

Thanks!

Answer 1

You can do this with conditional aggregation and row_number():

select chain,
       max(case when seqnum = 1 then branch end) as branch_01,
       max(case when seqnum = 2 then branch end) as branch_02,
       max(case when seqnum = 3 then branch end) as branch_03,
       max(case when seqnum = 4 then branch end) as branch_04
from (select t.*,
             row_number() over (partition by chain order by branch) as seqnum
      from table t
     ) t
group by chain;

Note: Your table doesn't have a column specifying the ordering of the rows. SQL tables represent unordered sets. Without such a column, there is no concept of one row being before or after another. So, this version orders by the branch name. You can order by whatever you like by changing the order by clause for row_number().