mysql - Dynamic rows into columns -

hi need able join 2 tables , return second table columns first table. need create (dependent first name, dependent last name, , dependent relationship) based on max number depid (which can dynamic).

thank in advance

table 1

+-------------+-------------+------------+ | employeeid  | first name  | last name  | +-------------+-------------+------------+ |           1 | bill        | johnson    | |           2 | matt        | smith      | |           3 | katy        | lewis      | +-------------+-------------+------------+ 

table 2

+-------------------------------------------------------------------+ | employeeid |dependent id  | first name | last name | relationship | +-------------------------------------------------------------------+ | 1               1            mary          johnson    spouse      | | 1               2            aaron         johnson     child      | | 2               1            eric          smith       child      | +-------------------------------------------------------------------+ 

expected output

+------------+------------+-----------+----------------------+---------------------+------------------------+----------------------+---------------------+------------------------+ | employeeid | first name | last name | dependent first name | dependent last name | dependent relationship | dependent first name | dependent last name | dependent relationship | +------------+------------+-----------+----------------------+---------------------+------------------------+----------------------+---------------------+------------------------+ |          1 | bill       | johnson   | mary                 | johnson             | spouse                 | aaron                | johnson             | child                  | |          2 | matt       | smith     | eric                 | smith               | child                  |                      |                     |                        | |          3 | katty      | lewis     |                      |                     |                        |                      |                     |                        | +------------+------------+-----------+----------------------+---------------------+------------------------+----------------------+---------------------+------------------------+              

you can dynamic sql & xml path example sql below

--table 1

create table #tmp1 (emp_id int, name char(10) ) insert #tmp1 values (1,'one') insert #tmp1 values (2,'two') insert #tmp1 values (3,'three')

--table 2

create table #tmp2 (emp_id int, dp_id int,fname char(10),rel char(10) ) insert #tmp2 values (1,1,'spouse one','spouse') insert #tmp2 values (1,2,'child one','child') insert #tmp2 values (2,1,'child two','child')

declare @cnt int , @ctr int = 0 , @sql varchar(max)

--get max dependent id

select @cnt = max(dp_id) #tmp2

--for verification select @cnt

--build dynamic sql dataset

set @sql = 'select emp_id '

while @ctr < @cnt

begin

set @ctr = @ctr+1 set @sql = @sql + ', ( select fname+'+''''+''''+'  #tmp2 #tmp1.emp_id = #tmp2.emp_id , #tmp2.dp_id = '+convert(varchar(2),@ctr)+' xml path ('+''''+''''+') ) fname'+convert(varchar(2),@ctr) set @sql = @sql + ',  ( select rel+'+''''+''''+'   #tmp2 #tmp1.emp_id = #tmp2.emp_id , #tmp2.dp_id = '+convert(varchar(2),@ctr)+' xml path ('+''''+''''+') )  rel'+convert(varchar(2),@ctr) 

end

set @sql = @sql+' #tmp1 '

--for verification print dynamic sql

select @sql

--execute dynamic sql

exec(@sql)