change query to use dynamic columns based on another table - with conditional aggregation

Question

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

date	id	type	currency
20230112	1	comm	usd
20230112	1	exch	usd
20230119	2	comm	usd
20230119	2	exch	gbp

result table

date	id	comm cur	exch cur
20230112	1	usd	usd
20230112	2	usd	gbp

any help would be greatly appreciated!

You need dynamic sql for this kind of thing. Search Dynamic cross tab on this site and you will find lots of nearly identical questions with solutions. — Sean Lange
– Sean Lange, Commented Feb 1, 2023 at 18:04

John Cappelletti · Accepted Answer · 2023-02-01 18:42:50Z

1

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)

edited Feb 1, 2023 at 18:42

answered Feb 1, 2023 at 18:35

John Cappelletti

82.3k7 gold badges50 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

solarissf Over a year ago

thank you @John. it works! The line I do not understand is the Pivot line. string_agg takes all expressions from rows and concats into single string. quotename, returns unicode string with bracket delimeters. would you mind explaining what this means in plain english? basically I see the values of my results are the max(currency), but I don't understand the select statement after that

Collectives™ on Stack Overflow

change query to use dynamic columns based on another table - with conditional aggregation

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related