Hi all,


At our company we use an external service to get some metering data. This data is returned as follows (simplified for this question):

id datapointreadingTypeIdreadingValue
11120
212555
3139
42122
522777
62310
73124
832999
93315


So one datapoint (consisting of three different metering types) is returned in 3 records.

We need this transformed to a row per datapoint where the meteringtypes are divided to the right column.

Like this:

datapointType 1Type 2Type 3
1205559
22277710
32499915


Is there any simple solution to accomplish this?

(for your information, in the example I mentioned only 3 meteringtypes. In our case we have at least 8 different metering types...)

For now I've got it sorted with a monster action (see image), but we will need more of these transformations in the near future...

Hi!

Hmm... Looks like you want to PIVOT the readingValue on the readingTypeId column.

See this: https://docs.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-server-ver15

You'll probably be able to replace that action with a SQL query.

It'll probably look like something like this:

SELECT [dataPoint], [1] AS [Type 1], [2] AS [Type 2], [3] AS [Type 3]

FROM **Entity**

PIVOT (

   SUM(readingValue)

    FOR [readingTypeId] IN ([1], [2], [3])

) AS P

Carlos Ribeiro da Fonseca wrote:

Hi!

Hmm... Looks like you want to PIVOT the readingValue on the readingTypeId column.

See this: https://docs.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-server-ver15

You'll probably be able to replace that action with a SQL query.

It'll probably look like something like this:

SELECT [dataPoint], [1] AS [Type 1], [2] AS [Type 2], [3] AS [Type 3]

FROM **Entity**

PIVOT (

   SUM(readingValue)

    FOR [readingTypeId] IN ([1], [2], [3])

) AS P


Hi, this looks like something that can be useable.

I will try it and let you know the results. Thanks so far.