how to use alias in unpivot function in snowflake?

0
votes

I am trying to convert this particular sql query to snowflake compatable format and the aliasing is not permitable in snowflake's unpivot function.So, can anyone please help me out in the conversion?

By un-commenting the part after 'as' keyword I am able to get the output.But as this data would be used later on due to I'm trying to find a way out.

Oracle:

UNPIVOT
(
attr_value FOR attr_name IN (EIKON_MESSENGER_FLAG  as 'USER_EIKON_MESSENGER_FLAG')
)

SQL compilation error: syntax error line 152 at position 58 unexpected 'as'.

1
sqlunpivotsnowflake-cloud-data-platform

1 Answers

0
votes

I prefer lateral joins for this, which Snowflake supports:

select attr_name, attr_value
from t cross join lateral
     (values ('USER_EIKON_MESSENGER_FLAG', t.EIKON_MESSENGER_FLAG)
     ) v(attr_name, attr_value);