How to unpivot any existing raw table?
I want to transpose columns to rows
here I am aware about stack can be used, but i dont want to give hard coded columns names... how this can be done run time (Without passing any hardcoded value of column name in stack command?)
How to get run time columns count & how this columns can be passed in stack?
Table1:
| id | p1 | p2 | p3 |
| 1 | 2.0 | 3.0 | 4.0 |
| 2 | 10.0 | 15.0 | 20.0 |
select id,
stack(3, 'p1', p1, 'p2', p2, 'p3', p3) as (property,value)
from `db1`.`Table1`
Best answer by Sonali Vishal Patil
Hi, I think the columns predictions is quite hard since with Raw you can have different number of columns per row in the same table. So depends on your final goal, here’s my example of using CTE as subquery, get_names from your tables and use it in the next query:
Check the
documentation
Ask the
Community
Take a look
at
Academy
Cognite
Status
Page
Contact
Cognite Support
with colNames as (
select get_names(*) as names from `myDb`.`myTable`
)
select names from colNamesAnd I think it’s easier to achieve this with Spark dataframe rather than Spark sql
+2We appreciate your contribution to our community hub! We have chosen to move your article to our hub's How-To section as it will greatly benefit other members of our community. Thank you for your understanding, and we look forward to seeing more great contributions from you in the future!
Best regards,
Dilini
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.