You can translate the question and the replies:

How to do the columns to row level convert in Denodo?

Hi team, Does denodo provide the function to do the columns to rows convert? Please refer to belwo table/data example for the transformation we want to do? Let us know how to do it in Denodo side? Thanks. **Before:** | date | region | book |account **|usd_spot_rate_mtd |usd_spot_rate_ytd |usd_spot_rate_qtd |jp_spot_rate_mtd |jp_spot_rate_ytd |jp_spot_rate_qtd |** | -------- | -------- | -------- | -------- | -------- | -------- | -------- | -------- | -------- | -------- | |20230401 | aej | abc | def | 100 | 200 | 300 | 400 | 500 | 600 | **After** |date | region | book | account **| currency | mtd | ytd | qtd |** | -------- | -------- | -------- | -------- | -------- | -------- | -------- | -------- | 20230401 | aej | abc | def | usd_spot_rate | 100 | 200 | 300 20230401 | aej | abc | def | jp_spot_rate | 400 | 500 | 600
unpivot piviot
user
30-08-2023 04:27:56 -0400
code

2 Answers

Hi team any update?
user
31-08-2023 03:47:25 -0400
Hi, You can achieved pivot with a combination of Selection and Union Views. You can find an example at [HOW TO PIVOT AND UNPIVOT VIEWS](https://community.denodo.com/kb/en/view/document/How%20to%20Pivot%20and%20Unpivot%20views). For instance, in VQL: ``` create or replace view column_to_row_example as select date, region, book, account, 'usd_spot_rate_mtd' as currency, usd_spot_rate_mtd as mtd, usd_spot_rate_ytd as ytd, usd_spot_rate_qtd as qtd from my_database.<your_base_view> union all select date, region, book, account, 'jp_spot_rate_mtd' as currency, jp_spot_rate_mtd as mtd, jp_spot_rate_ytd as ytd, jp_spot_rate_qtd as qtd from my_database.<your_base_view>; ``` Hope this helps
Denodo Team
31-08-2023 06:57:19 -0400
code
You must sign in to add an answer. If you do not have an account, you can register here