SQL Server query: How to get a single record, from an inner join

If the number of columns that the result has is predictable, you can use the clause pivot of SQL Server to achieve your goal. This operation transforms rows into columns, using an aggregation function.

To achieve the exact result, I first calculate the column number , with the concatenation between 've' and the row number corresponding to each record by dividing it into groups by idt .

Finally, I do the pivot using the value of this column. In this case, I am including only 2 columns [ve1] and [ve2] . This can be easily extended, provided that, as I said, the number of columns is predictable.

For the aggregation, since there really is nothing to add, and the resulting column is of type char or similar, I use min() .

The query would look something like:

with tr as (
select 1 idt, '1101' vt
union all select 2, '1102'
union all select 3, '1103'
)
,
te as (
select 1 ide, 'E101' VE, 1 idt
union all select 2, 'E102', 1
union all select 3, 'E103', 2
union all select 4, 'E104', 3 
)
,
base as (
select   te.idt
       , tr.vt
       , te.ve
       , 've' + cast(row_number() over (partition by te.idt order by te.ve) as varchar) num_columna
  from te
       inner join tr on tr.idt = te.idt
)
select idt, vt, [ve1], [ve2]
  from base
       pivot (min(ve)
         for num_columna in ([ve1], [ve2]) ) as PivotTable

Which produces the following result:

idt         vt   ve1  ve2
----------- ---- ---- ----
1           1101 E101 E102
2           1102 E103 NULL
3           1103 E104 NULL

(3 rows affected)