I have sensor data in a SQL Server 2016 table that looks like the following:

For every temperature record, there is usually, but not always, a corresponding humidity reading (there could be humidity row, but no corresponding temp or vice versa). They are tied together with the PairIdentity column. I want to produce a query that Groups the data on the PairIdentity and Pivot it into a single row for each grouping with new columns. I'm not sure if I've worded that correctly but I'm trying to achieve a result similar to this:

Any tips or help would be greatly appreciated.

CodePudding user response：

This is just a standard case pivot:

select
    min(Created) as Created,
    min(case when tag = 'temperature' then RealValue end) as Temp,
    min(case when tag = 'temperature' then Units end) as TempUnit,
    min(case when tag = 'humidity' then RealValue end) as Humidity,
    min(case when tag = 'humidity' then Units end) as HumidityUnit,
    PairIdentity,
    min(PairConfigLabel) as PairConfigLabel
from T
group by PairIdentity

CodePudding user response：

From your sample data it appears you just need an inner join between the two data sets:

with tmp as (
    select * 
    from t
    where tag='temperature'
), hum as (
    select * 
    from t
    where tag='humidity'
)
select
    t.Created Created, 
    t.RealValue Temp,
    t.Units TempUnit,
    h.RealValue HunidityUnit,
    t.PairIdentity,
    t.PairConfigLabel
from tmp t join hum h on t.PairIdentity=h.PairIdentity;

CodePudding user response：

I don't know if I understand very well your data or structure, or what you want, please comment here and I'll glad to update or correct my answer.

Main idea is get "two tables", one for temp and another for humidity, and join them by (if are unique between meditions) your PairIdentity. Then show specific data as required for the joined table.

You can play with "this join" and the real data to get more precision.

    SELECT 
        temp.Created,
        temp.temperature as Temp,
        temp.Units as TempUnit,
        hum.humidity as Humidity,
        hum.Units as HumidityUnit,
        temp.PairIdentity,
        temp.PairConfigLabel
        FROM
        (
        SELECT
        Created,
        RealValue as temperature,
        Units,
        PairIdentity,
        PairConfigLabel
        FROM yourTable
        WHERE Tag = 'temperature'
        ) as temp
        JOIN
        (
        SELECT 
        Created,
        RealValue as humidity,
        Units,
        PairIdentity,
        PairConfigLabel
        FROM yourTable
        WHERE Tag = 'humidity'
        ) as hum
        ON temp.PairIdentity = hum.PairIdentity