function: pivot and unpivot

[wangsimo0]

Feature request

It's better to have pivot function to support more flexible scenario. By now, we can only use case when to do pivot, and it has bad performance. for unpivot, we can use unnest, but still need an aggregation.
https://learn.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-server-ver15

[hongism]

Can I have a try?

[kateshaowanjou]

Hi @hongism, I've noticed that you are new to the StarRocks community.
If you have any questions, feel free to join WeChat for discussion, the community will assist you with the function task: https://853921.ma3you.cn/articles/b12e90J/

[macroguo-ghy]

Hi, I am interested in PIVOT syntax.
I think we have two ways to implement it:

• See PIVOT as a syntax sugar, we can transform pivot to agg(case ... when ...)
• Add a new PIVOT operator

what do you think? @wangsimo0 @kateshaowanjou