How to group a paricular column in a row of data, so that it could be used as in coalese

Database
Fan

How to group a paricular column in a row of data, so that it could be used as in coalese

I have a table with the following data

Stn A Stn B Class rate rank
AAA BBB FIRST 0.00 1
AAA BBB SECOND 18.27 2
AAA BBB THIRD 0.00 3
AAA BBB FOURTH 15.40 4

now I need a query which results the following ,

If class is First then I need rate to be coalesce(rate(rank1), rate(rank2), rate(rank3), rate(rank4), 0)
Similarly
If class is Second then I need rate to be coalesce(rate(rank2), rate(rank3), rate(rank4), 0)
for Third Class rate to be coalesce(rate(rank3), rate(rank4), 0)
for Fourth class rate to be coalesce(rate(rank4), 0)

Could you please let me know how to get this result by using the functions in teradata?
2 REPLIES
Junior Contributor

Re: How to group a paricular column in a row of data, so that it could be used as in coalese

I really don't understand your question, but you can use CASE to return NULL for unwanted values, like:

coalesce(min(case class when 'first' then rate end),
min(case class when 'second' then rate end),
min(case class when 'third' then rate end),
min(case class when 'fourth' then rate end))

You should try to rephrase your question, include DDL/Inserts and tell the exact business rules.

Dieter
Fan

Re: How to group a paricular column in a row of data, so that it could be used as in coalese

Thanks Dieter for the reply I have solved the query now... I have used two work tables and a Partition by Clause to getthe desired result.. Anyways thanks for ur reply..