0

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

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
Improve this question
2
  • Where are id=2 records in your output table? Commented Jan 24, 2023 at 19:29
  • sorry, I fixed result table Commented Jan 24, 2023 at 19:31

2 Answers 2

Reset to default
1 
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

EDIT: How it works? If we removed MAX() and GROUP BY:

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

would give this result:

date id Comm_cur Exch_cur
2023-01-12 1 usd null
2023-01-12 1 null usd
2023-01-19 2 usd null
2023-01-19 2 null gbp

If we want to reduce those rows to 1 per Date, id we need a GROUP BY. And a GROUP BY requires that we have an aggregation function for all fields that are not listed in GROUP BY list (Date and Id). MAX() and MIN() are aggregation functions that we could use on a string type and the aggregation wouldn't take NULL into account, just what we need. In other words, we are using MAX() or MIN() just to eliminate NULLs. Thus applying MAX() or MIN() and GROUP BY:

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

results:

date id Comm_cur Exch_cur
2023-01-12 1 usd usd
2023-01-19 2 usd gbp

DBfiddle demo

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

7 Comments

thanks. I'm trying to understand max on strings. from what I am reading it says something about alphabetical order. Is this working because when type is not equal to something it returns null.... and max is returning something greater than null?
@solarissf, it doesn't need to be MAX(). It could be MIN() too. It is there just because we are using GROUP BY and thus we need an aggregation function. If you think about it, we didn't use else part which is NULL by default. So for example, [20230119] for Comm_cur, it is saying select MAX() (or MIN() if you wish) of 'usd' and NULL (when type='comm' it is 'usd' else -'exch'- NULL). On Exch_cur column, we are checking MAX() of 'gpb' and NULL. To understand it by visualizing, remove MAX() and group by and check the result. MAX() is a 'trick' there to say otherwise to GROUP BY those rows into 1.
@solarissf, I added some visualization about how it works in edit. I hope it is easier to understand now.
thank you! this clears up my confusion
sorry to bring up another question, is it possible to make this dynamic based on another table. in this example I would have a table that lists.. 'comm', 'exch', and I would want the same results
|
1

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
Improve this answer

5 Comments

thanks John. what did you amend with my table on original post? I'm trying to understand what I did wrong. It looked good in the preview.
@solarissf added line breaks so tables would present correctly They were a single line
thank you, would you mind explaining the use of max? from what I see on a string, it returns the greatest alphanumeric. does this work because the other it returns are null?
@solarissf It is the max value based on the intersection of date ,id and type (comm vs exch) . Just for fun, run the query without the aggregation and group by Select date ,id ,comm_cur = case when [type]='comm' then currency end ,exch_cur = case when [type]='exch' then currency end From YourTable
@solarissf why ask me? You accepted another answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.