--create table tableA (id int,name varchar(10))
--insert tableA
--select 1,'AAA' union all
--select 2,'BBB' union all
--select 3,'CCC'
--create table tableB (name varchar(10),value varchar(10))
--insert tableB
--select 'AAA','123123' union all
--select 'AAA','12312' union all
--select 'CCC','1231' union all
--select 'CCC','123' union all
--select 'CCC','12' union all
--select 'SSS','xxx'
-- GO
SELECT CASE WHEN id2<>1 THEN '' ELSE cast(id AS varchar) END id,CASE WHEN id2<>1 THEN '' ELSE NAME END NAME,value
FROM (
SELECT a.id,a.NAME,b.value,ROW_NUMBER()OVER(PARTITION BY a.id,a.NAME ORDER BY b.value DESC)id2
FROM tablea a LEFT JOIN tableB b ON a.NAME=b.name)a
/*
id NAME value
------------------------------ ---------- ----------
1 AAA 123123
12312
2 BBB NULL
3 CCC 1231
123
12
*/
