SQL Server, Merge two records in one record -

SQL Server, Merge two records in one record -

we have these tables

create table tbl01 ( [id] int not null primary key, [name] nvarchar(50) not null ) create table tbl02 ( [subid] int not null primary key , [id] int not null references tbl01(id), [val] nvarchar(50) null, [code] int null )

if run query:

select tbl01.id, tbl01.name, tbl02.val, tbl02.code tbl01 inner bring together tbl02 on tbl01.id = tbl02.id

we these results:

------------------------------- id | name | val | code ------------------------------- 1 | 1 | firstval | 1 1 | 1 | secondval | 2 2 | 2 | yourval | 1 2 | 2 | ourval | 2 3 | 3 | notval | 1 3 | 3 | thisval | 2 -------------------------------

you can see each 2 rows related same "id"

the question is: need each id retrieve 1 record val, each val homecoming in column according value of column code

if(code = 1) val val-1 else if (code = 2) val val-2

like this:

------------------------------- id | name | val-1 | val-2 ------------------------------- 1 | 1 | firstval | secondval 2 | 2 | yourval | ourval 3 | 3 | notval | thisval -------------------------------

any advice?

use can utilize max , grouping accomplish

select id, name, max([val1]) [val-1], max([val2]) [val-2] ( select tbl01.id, tbl01.name, case code when 1 tbl02.val else '' end [val1], case code when 2 tbl02.val else '' end [val2] tbl01 inner bring together tbl02 on tbl01.id = tbl02.id ) tbl grouping id, name

sql sql-server