Solved

How to unpivot any existing raw table? [Community Contributed]

  • 21 March 2023
  • 4 replies
  • 95 views

Userlevel 2
Badge

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`

icon

Best answer by Sonali Vishal Patil 30 March 2023, 11:47

View original

4 replies

Userlevel 2
Badge

also I need help to understand how output of one query can be passed as input to another query?

Userlevel 1
Badge

 

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:

with colNames as (
select get_names(*) as names from `myDb`.`myTable`
)
select names from colNames

And I think it’s easier to achieve this with Spark dataframe rather than Spark sql

https://stackoverflow.com/questions/60896406/how-to-unpivot-spark-dataframe-without-hardcoding-column-names-in-scala

Userlevel 2
Badge

@Vu Hai Nguyen  Thanks for the response….I had some other work around to achieve my goal...in raw table I have ingested datatypes of all the columns & I am referring that to resolve my purpose. with the help of joins & using sub queries it got sorted of :)

Userlevel 4
Badge +2

Hi @Sonali Vishal Patil,

We 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 

Reply