I am trying to convert this query to using dynamic columns (from another table). In this example I want to replace the 2 areas where I have type='comm' and type = 'exch' to be dynamic. (not hardcoded in this line) The types will be found in another table.

HERE IS MY ORIGINAL QUERY

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 THE TABLE I WANT TO USE FOR THE 'TYPE'

insert into #fm (type) values
        ('comm')
        ,('exch')

when I google this concept I get dynamic pivot tables as possible solutions but that doesn't see what I am looking to do.

source table

result table

any help would be greatly appreciated!

CodePudding user response：

Dynamic columns require Dynamic SQL

Here is a working option that will also filter the source table based on your #fn

Note: If not 2017 , you can replace the string_agg() with the stuff/xml approach.

Example

Declare @SQL varchar(max) = '
Select *
 From  (  
Select A.date
      ,A.id
      ,type = A.type '' cur''
      ,A.currency
 From YourSourceTable A
 Join #fn B on A.type=B.type
       ) src
 Pivot ( max(currency) for [type] in ('   (select string_agg(quotename(type ' cur'),',') from #fn )   ') ) pvt
'
Exec(@SQL)

Results

date        id  comm cur    exch cur
2023-01-12  1   usd         usd
2023-01-19  2   usd         gbp

Just in case you need the stuff/XML

Declare @SQL varchar(max) = '
Select *
 From  (  
Select A.date
      ,A.id
      ,type = A.type '' cur''
      ,A.currency
 From YourSourceTable A
 Join #fn B on A.type=B.type
       ) src
 Pivot ( max(currency) for [type] in ('   stuff((Select Distinct ','  quotename(type ' cur') From #fn  For XML Path ('')),1,1,'')   ') ) pvt
'
Exec(@SQL)