How can I correctly handle sparse crosstabs?

Started by Rob Richardsonabout 12 years ago2 messagesgeneral

RDRichardson@rad-con.com

about 12 years ago

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