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!

possible duplicate of [How to create a PivotTable in Transact/SQL?](http://stackoverflow.com/questions/11617713/how-to-create-a-pivottable-in-transact-sql) — Taher Rahgooy, Aug 18 '15 at 15:33
Its not a pivot table. A pivot table would have the values of the cells are columns, we are not doing the same — BigScratch, Aug 18 '15 at 15:56
Of course that's a Pivot Table, unfortunately Teradata doesn't support a PIVOT function. But to get a result for an unknown number of columns you will always need some dynamic SQL in a Stored Procedure creating the needed SQL on the fly.... — dnoeth, Aug 18 '15 at 17:20
Correct me if I'm wrong, but a 'traditional' pivot table has in each new column one of the unique values contained in the groups, so the new columns in our case would be UK,US,ISRAEL,FRANCE,BELGIUM,NIGERIA, whereas the values in each of these columns would correspond to an aggregate function on a third initial column such as sum, mean, var or any function that we like. — BigScratch, Aug 19 '15 at 16:40
It's the same logic as a pivot table :-) Btw, why do you want to create a denormalized table? — dnoeth, Aug 20 '15 at 05:40
The results is the same more or less yes i agree :) Like that it is easy to find how many have one branch, two branches etc, when you dont really care about the countries. Another thing is that like that you avoid enumerate over the countries (which would be around 200) but you only care about the set of countries per company — BigScratch, Aug 20 '15 at 09:22

score 4 · Accepted Answer · answered Aug 18 '15 at 15:30

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().

Thanks! In that case though, this assumes that we know the maximum number a priori. I am more interested in a more general 'pattern', where these columns could be extracted automatically. For my case, this solution should work though :) — BigScratch, Aug 18 '15 at 15:37
@BigScratch . . . A SQL query defines all its columns up-front. Hence, you cannot do what you want with a single query. You would need to use dynamic SQL. — Gordon Linoff, Aug 18 '15 at 22:34

sql create columns from group by collection

1 Answers1