|
It seems that crosstab queries assume there is going to be a value for every possible row in the source query. That’s not what I have, and I am not getting the result I need. Here is the definition of my source table: CREATE TABLE plc_values ( plc_values_key bigserial NOT NULL, recorded_date timestamp with time zone, tag_name character varying(120), tag_value character varying(120), CONSTRAINT plc_values_pk PRIMARY KEY (plc_values_key ) ) There are 30 possible tag_name values. But for a given recorded_date, there may only be one tag, and the most that I see is five tags. When I run the crosstag query, the data columns are filled in from left to right, regardless of the
name of the column. Here is the query: select * from crosstab($$ select recorded_date, tag_name, tag_value from plc_values$$ ||
$$ order by recorded_date, tag_name $$ ) as ct( read_time timestamp with time zone, Crane2_Crane_Loaded character varying(120), Crane2_Crane_LoadWeight character varying(120), <28 more fields snipped> ) I always get a value in the Crane2_Crane_Loaded field. It may not have come from that tag, though. Sometimes the value is “True” or “False”, or maybe 1 or 0, or maybe even 102598.
If crane2_crane_loadweight has a value, it is guaranteed that crane2_crane_loaded has a value. In general, if a column has a value, then the column to its left has a value.
There is no relation between the value in the column and the tag name the value was associated with in the source table. How can I enforce that relationship in a crosstab? Or am I plain out of luck? Thanks very much! RobR |
