How to prevent over using SELECT just for column name?

Question

as titled, is there any way I can make it in a way so that I don't have to call so many query just for column name?

select val,  
(select val1 FROM data WHERE size='a' AND cat ='1' ) as cat1_a,  
(select val1 FROM data WHERE size='a' AND cat ='2' ) as cat2_a,  
(select val1 FROM data WHERE size='b' AND cat ='1' ) as cat1_b,  
(select val1 FROM data WHERE size='b' AND cat ='2' ) as cat2_b,  
(select val1 FROM data WHERE size='c' AND cat ='1' ) as cat1_c,  
(select val1 FROM data WHERE size='c' AND cat ='2' ) as cat2_c  
from data  

Answer 1

it doesn't really save much for characters / typing but you could use join.

select t1.val, a1.val1 as cat1_a, a2.val1 as cat2_a
from data t1
left join data a1
    on t1.val=a1.val
    and a1.size = 'a'
    and a1.cat = '1'
left join data a2
    on t1.val=a2.val
    and a2.size = 'a'
    and a2.cat = '2'

I don't know what your data looks like, but you could try some different results with this https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=a95ad5bd5c593c72354c24f9f867694a