Is there a way to merge 2 table into 1 line

Question

I am trying to merge 2 tables into 1 column. Is there anyway I can do it?

For example, I have Table 1 and Table 2 which looks like

Table1:

unit    Item
1   apple
2   ball
3   cat
4   dog
5   elephant

Table2:

unit    Field1
1   test1
1   test2
2   apple1
2   test1
3   ball1
3   cat1
4   dot1
4   elp
5   rat
5   rat1
5   rat2

If I use:

Select * from table1 as  a  left join table2 as b on a.unit = b.unit, 

I will get multiple rows as there are multiple units in table 2.

What I want is

Unit item field1_1 field1_2   field1_3
1    apple test1  test2       null
2    ball  apple1  test1      nul
3    cat ...................
4    dog..............
5    elephant  rat   rat1    rat2

Is there anyway I can get the result?

Thank you

Answer 1

You can use row_number to generate a sequence number within each unit and then use conditional aggregation to pivot the data

select unit,
    item,
    max(case when seqnum = 1 then field end) as field1,
    max(case when seqnum = 2 then field end) as field2,
    . . .
    max(case when seqnum = 9 then field end) as field9
from (
    select t1.unit,
        t1.item,
        t2.field,
        row_number() over (
            partition by t1.unit order by t2.field
            ) as seqnum
    from table1 t1
    join table2 t2 on t1.unit = t2.unit
    ) t
group by unit,
    item;