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

  • 21 March 2023
  • 4 replies

Userlevel 2

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?


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 30 March 2023, 11:47

View original

4 replies

Userlevel 2

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

Userlevel 1


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

Userlevel 2

@Vu Hai Nguyen  Thanks for the response….I had some other work around to achieve my 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,