sqlsql-server

Query to flatten a few query records


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.


Solution

  • 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