can someone help guide me into converting the data from source table into my desired result table?

From the source, I want to create new column names, based on the "type's" listed in the source, but the values being used to populate would be from "currency"? Hope that makes sense. I have created the result table to display what I am trying to create.

source table

result table

CodePudding user response：

select [date], id, 
  max(case when type='comm' then currency end) as Comm_cur,
  max(case when type='exch' then currency end) as Exch_cur
from myTable
group by [date], id;

Here is DBFiddle demo

CodePudding user response：

Two quick options.

Conditional Aggregation. Often more performant and offers more flexibility

Select date
      ,id
      ,comm_cur = max( case when [type]='comm' then currency end )
      ,exch_cur = max( case when [type]='exch' then currency end )
 From  YourTable
 Group by date,id

PIVOT - comes with some restrictions

Select *
 From  YourTable src
 Pivot ( max(currency) for [type] in ([comm],[exch]) ) pvt