Skip to main content

Hi,

I have data in raw table in this format:

Table1:

id p1 p2 p3
1 2.0 3.0 4.0
2 10.0 15.0 20.0

 

I wanted to transform this details into following format through transformation

 

id property value
1 p1 2.0
1 p2 3.0
1 p3 4.0
2 p1 10.0
2 p2 15.0
2 p3 20.0

 

 

so I have written following transformation query:

SELECT id, property, value
FROM `db1`.`Table1`
UNPIVOT (value FOR property IN (p1, p2,p3)) UP

 

This query works in SQL studio, but in COGNITE transformation UI , I see below error:

Mismatched input 'FOR' expecting {')', ',', '-'}(line 3, pos 15)

 

Can you please help to understand what is missing in this? 

 

Hi @Niranjan Madhukar Karvekar

UNPIVOT function is not supported in CDF transformations. Please refer to the documentation for further reference. But you can use the stack function as a workaround. Please find the query as below;

select id, 
  stack(3, 'p1', p1, 'p2', p2, 'p3', p3) as (property,value)
from `db1`.`Table1`

I hope this will help you.

Best regards,
Dilesha

Thank you Dilesha, this works. 

Reply


Terms & ConditionsCookie Settings