Odtnhj

I am using sqlite db from an iphone.

I have a chartdatatable as shown below.

The deviceTypeHash is a hash for a device that can have 2 or 3 SeriesNameHash.

The master table for DevicTypeHash comes from DeviceParamUnit table as shown below

As you'll observe the DeviceTypeHash -represents a device - is common for the first three records and they have 3 different SeriesNameHash which corresponds to Systolic, Diastolic and PulseRate in the DeviceParamUnit table. They are like parameters of the first device which is a BP monitor. (which is True, Blood Pressure has three readings - systolic, dia...)

Similar way, the next device is the Temperature Wand and it has 11 parameters represented by 11 unique series name hash.

What I require is that when I provide the query with DeviceNameHash, it should show me the values (column is present in chartdatatable) for its corresponding SeriesNameHash(parameters). But it should show it pivoted.

Here's what I tried to get the pivot.

SELECT time
 , MAX(CASE WHEN SeriesNameHash = -7656137604397428413 THEN value ELSE 0 END) as '-7656137604397428413'
 , MAX(CASE WHEN SeriesNameHash = 1538157359584988292 THEN value ELSE 0 END) as '1538157359584988292'
 , MAX(CASE WHEN SeriesNameHash = 6366213907772730014 THEN value ELSE 0 END) as '6366213907772730014'
 , MAX(CASE WHEN SeriesNameHash = -5604390693208903701 THEN value ELSE 0 END) as '-5604390693208903701'
 , MAX(CASE WHEN SeriesNameHash = -5604390693208899606 THEN value ELSE 0 END) as '-5604390693208899606'
 , MAX(CASE WHEN SeriesNameHash = -5604390693208928275 THEN value ELSE 0 END) as '-5604390693208928275'
 , MAX(CASE WHEN SeriesNameHash = -5604390693208924180 THEN value ELSE 0 END) as '-5604390693208924180'
 , MAX(CASE WHEN SeriesNameHash = -5604390693208920081 THEN value ELSE 0 END) as '-5604390693208920081'
 , MAX(CASE WHEN SeriesNameHash = -5604390693208915986 THEN value ELSE 0 END) as '-5604390693208915986'
 , MAX(CASE WHEN SeriesNameHash = -5604390693208944655 THEN value ELSE 0 END) as '-5604390693208944655'
 , MAX(CASE WHEN SeriesNameHash = -5604390693208940560 THEN value ELSE 0 END) as '-5604390693208940560'
 , MAX(CASE WHEN SeriesNameHash = -5604390693208936461 THEN value ELSE 0 END) as '-5604390693208936461'
 , MAX(CASE WHEN SeriesNameHash = -5604390693208932366 THEN value ELSE 0 END) as '-5604390693208932366'
 , MAX(CASE WHEN SeriesNameHash = 4940014074620696541 THEN value ELSE 0 END) as '4940014074620696541'
 , MAX(CASE WHEN SeriesNameHash = 4940014074620704732 THEN value ELSE 0 END) as '4940014074620704732'

 FROM chartdatatable
 GROUP BY time

with result.

( I know I could have named the columns 'systolic', 'diastolic'...but that's beside the point.

The problem is, now, How do I dynamically only display those columns(each column has the SeriesNameHash) when the query is only supplied a DeviceTypeHash.

E.g - If I needed to see all the values (by time) of the B.P Monitor, its seriesHashName would be something like this

SELECT seriesname, seriesnamehash FROM 'DeviceParamUnitTable' where DeviceTypeHash = 6814980142680378626

Systolic -7656137604397428413
Diastolic 1538157359584988292
Pulse Rate 6366213907772730014

How do I modify the parent query to only get the first three columns of the pivot result when supplied by the DeviceTypeHash of BP monitor and get (say) column 4-14 when supplied by the DeviceTypeHash of (say) a TemperatureWand. Currently, it shows all parameter values and its not useful when I require the pivot made above, filtered.

To summarize, I've done a pivot, but how do I now get specific columns
when provided by a value that has those column names mapped to it.

If this is not possible by a query, view, any other approach can be suggested. Any help will be appreciated.