Query to flatten a few query records

Question

I need a query in SSMS 2019 to change this data:

Key  Value  Count
-----------------
  1    2      2
  1    4      1  

BUT I don't know which SQL query commands to get me the needed result as a single row as shown here:

Key   NormalValue   SpecialValue
--------------------------------
 1        2              1

• The NormalValue is the COUNT() for the value of 2 (=2).
• The SpecialValue is the COUNT() for the value of 4 (=1).

To clarify the query DATA is Key (standard), value is the importance-value for the Key-record and the Count is the number of related records that have the importance of either 2 or 4 or 8 or other combinations (1,2,4,8). The wanted result is to show the Count()'s for importance-value of 2 and 4 in a single records for the KEY.

Answer 1

You can use conditional aggregation to achieve your results. But it's still unclear why you have a desired output of 4 rather than 1 for your special value. I presume that's a typo because it contradicts your explanation.

create table my_table (
  ikey integer, 
  ivalue integer, 
  icount integer
  );

insert into my_table values 
(1, 2, 2), 
(1, 4, 1), 
(2, 2, 10);

select 
  ikey, 
  max(case when ivalue = 2 then icount end) as normal_value, 
  max(case when ivalue = 4 then icount end) as special_value
from my_table
group by ikey

ikey	normal_value	special_value
1	2	1
2	10	null

fiddle