Thread: How can I correctly handle sparse crosstabs?

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