AnsweredAssumed Answered

PI SQL Queries - Transpose Element Attribute Values without using the DataT.ft_Transpose functions

Question asked by alan.mccarthy on Aug 8, 2017
Latest reply on Nov 17, 2017 by zytay

I'm currently working on reading the elements and their attributes from our PI AF. The ideal sql query would have each element and certain (or all) attributes on a single row, with the attributes being columns. I know that this can be done using the ft_transposeSnapshot dataT tables, but we have a large amount of different templates.

Is it possible to achieve this without using the transposesnapshots?


A sample of the ideal result is below, with the element name and element ID as columns 1 and 2, and the remaining columns being attribute names.


NameElementIDANSI device numberLocation - LatitudeLocation - LongitudeModel NumberRDSPP - CodeBatch NumberDocuments - DrawingHub HeightLandownerManufacturerRotor DiameterSerial NumberSite NameTurbine Name
Wind Turbine1234ckd93kljas09usdfg125254.33-6.55000AN Other08000PI08 WTG008
Wind Turbine Enercon1234ckd93kljas09usdfg135354.33-6.5500AN Other070PI08 WTG007
Wind Turbine Enercon1234ckd93kljas09usdfg146754.35-6.5500AN Other 2050PI08 WTG006
Wind Turbine Enercon1234ckd93kljas09usdfg158954.33-6.5500AN Other040PI08 WTG005
Wind Turbine Enercon1234ckd93kljas09usdfg168954.33-6.5500AN Other030PI08 WTG004
Wind Turbine Enercon1234ckd93kljas09usdfg175254.33-6.5500AN Other040PI08 WTG003
Wind Turbine Enercon1234ckd93kljas09usdfg183054.66-6.5500AN Other 3050PI08 WTG002
Wind Turbine Enercon1234ckd93kljas09usdfg193154.33-6.5500AN Other060PI08 WTG001
Wind Turbine Enercon1234ckd93kljas09usdfg202354.33-6.5500AN Other 3070PI08 WTG000
Wind Turbine Goldwind1234ckd93kljas09usdfg213554.33-6.5500AN Other080PI08 WTG001