1

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,

Koushur
  • 167
  • 1
  • 9

1 Answers1

3

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
Simon D
  • 5,730
  • 2
  • 17
  • 31
  • HI Simon, I have updated the question to be more precise what i exactly want to do in snowflake. The answer you replied may not work as I rely here on the result set from TABLE(RESULT_SCAN(LAST_QUERY_ID(). – Koushur Mar 31 '21 at 22:44
  • Answer is been updated again based on your new requirements – Simon D Mar 31 '21 at 23:07