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

Possible duplicate of [SQL Server dynamic PIVOT query?](https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query) — Sean Lange, May 23 '17 at 16:15

score 1 · Accepted Answer · answered May 23 '17 at 16:15

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;

Thank you. This is what I was looking for. – Sam May 23 '17 at 16:29 — Sam, May 23 '17 at 16:29

score 0 · Answer 2 · edited May 24 '17 at 06:16

0

Try to use GROUP_CONCAT(Feild1) :

Select a.unit , a.item , GROUP_CONCAT(Feild1) as feild1 from table1 as a left join table2 as b on a.unit = b.unit group by a.unit , a.item

Result :

Unit    Item      Feild1
1         apple     Test1,test2
........... ....

edited May 24 '17 at 06:16

Ajay2707

5,690
6
40
58

answered May 23 '17 at 16:29

Saleh Mosleh

504
5
12

Is there a way to merge 2 table into 1 line

2 Answers2