Transformation to RAW

  • 25 January 2023
  • 2 replies


I have data in raw table in this format:


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? 



Best answer by Dilesha Kodithuwakku 27 January 2023, 15:47

View original

2 replies

Userlevel 1

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,

Thank you Dilesha, this works.