Is there a way to rotate column's to row in snowflake?

Question

I have table with single row like below which comes from snowflake query like below

show warehouses like 'COMMON_WH';
select "name","state","type","size" FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))

name|state|type|size
COMMON_WH|SUSPENDED|STANDARD|Small

I Want to rotate it like below.

name| COMMON_WH
state|SUSPENDED
type|STANDARD
size|Small

Thanks,

Answer 1

You can do this with a unpivot. Here is an example of how to use it. Note that it is a bit verbose because you need to cast all of the original table's datatypes to the same datatype before the unpivot (varchar in this case).

show warehouses like 'COMMON_WH';

select *
from (
    select
        "name"::varchar  as name,
        "state"::varchar as state,
        "type"::varchar  as type,
        "size"::varchar  as size
    FROM TABLE (RESULT_SCAN(LAST_QUERY_ID()))
) unpivot (col_val for col_name in (name, state, type, size))

This produces:

COL_NAME	COL_VAL
NAME	COMMON_WH
STATE	STARTED
TYPE	STANDARD
SIZE	X-Small