Pivot data T-SQL

I have the following table:

I want to pivot it to the following table:

I tried to work with the following example: https://www.sqlshack.com/dynamic-pivot-tables-in-sql-server/ But in this case a SUM operator is mandatory. I don’t want to use the SUM operator for my data.

What would be the best approach to go. Eventually I want to use the data again, I want to prevent that I need to make a lot of “left join” statements like the following:

select table_a.Cat,one.number as number1, one.namevalue as Name1, two.number as number2, two.namevalue as name2
from table_a 
left outer join (
    select *
    from #temp
    where numbercat = 'number1'
) as one on table_a.cat = one.cat
left outer join (
    select *
    from #temp
    where numbercat = 'number2'
) as two on table_a.cat = two.cat

I am able to unpivot the numbercat & number with the following code:

select *
from 
(
  select cat, numbercat, number
  from #input
) src
pivot
(
  min(number)
  for numbercat in ([1], [2], [3])
) piv;

What should I do to also incorporate the Namecat & namevalue data?

Answer

I find that conditional aggregation is usually simpler and more versatile than pivot:

select cat,
       max(case when numbercat = 'number1' then number end) as number1,
       max(case when numbercat = 'number2' then number end) as number2,
       max(case when namecat = 'name1' then name end) as name1,
       max(case when namecat = 'name2' then name end) as name2
from #temp
group by cat;