SQL: Turning Rows into Columns for Variable Number of Rows

Question

I have two tables whose simplified structure looks like this:

RESPONSES
id
created

ACCESSORY VALUES
id
response_id
sensor_id
value

I want to create a view that flattens all accessory values for a given response into one row over a time period (filtering on response.created). I think I want a Pivot table or Crosstab, but I'm unfamiliar with both and the examples I've found mainly deal with a known number of columns. To complicate things, a given sensor could only appear for part of the time period if a user started or stopped tracking it during the time in question. Ideally I'd hold a NULL in that column for the sensor in any rows when it was not present. Is this possible? If so, am I on the right track or looking in the wrong place?

The SQL to get the data as individual rows looks like

SELECT r.id, a.sensor_id, a.value from results_response r 
INNER JOIN results_accessoryvalue a ON r.id = a.response_id
WHERE r.installation_id = 40 
AND r.created BETWEEN '2013-04-01' AND '2013-05-01' 
ORDER BY r.created

but I'm not having any luck trying to use it in a crosstab because I don't know how to specify dynamic columns.

Thomas · Accepted Answer · 2013-05-15 10:49:54Z

you should use crosstab with some additons. i had this problem too and solved it for my proposal like this.

first install crosstab extension

the trick i used is to create 2 additional functions. one to get the type information, needed as resultset for crosstab function. look at this:

CREATE OR REPLACE FUNCTION "TEMPORARY.TYPE.FROM.COLUMN"(text, text)
  RETURNS text AS
$BODY$
DECLARE
    typestring TEXT;
    returnrec RECORD;
BEGIN
    typestring := '';
    FOR returnrec IN EXECUTE $1 LOOP
        typestring := typestring||', "'||returnrec."Column"||'" '||$2;
    END LOOP;

  RETURN typestring;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

Now we can write our second function:

CREATE OR REPLACE FUNCTION "DYNAMIC.CROSSTAB"(text, text, text)
  RETURNS text AS
$BODY$
DECLARE
    typestring TEXT;
    executestring TEXT;
BEGIN
    DROP TABLE IF EXISTS "TBL.Crosstab";
    SELECT "REPORTING"."TEMPORARY.TYPE.FROM.COLUMN"($2,$3) INTO typestring;
    executestring := 'CREATE TEMPORARY TABLE "TBL.Crosstab" AS (SELECT * FROM crosstab('''||$1||''','''||$2||''') AS (row_name DATE'||typestring||'));';
    EXECUTE executestring;
  RETURN '"TBL.Crosstab"';
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

To create your crosstab simply call:

SELECT * FROM "DYNAMIC.CROSSTAB"(text, text, text);

It returns the name of an temporary table for your session filled with your result. I needed it this way.

Parameter explanation:

First = The query to get your data (must have 3 columns: row_name, cat and value)

Second = The query to get your columns wich returns all your categories (cat)

Third = The columntype for our temporary type

it's not perfect but fit's our needs. we have statements, fetching more than 450 coloumns and some thounds rows at this way. hope it helps

Clodoaldo Neto · Accepted Answer · 2013-05-10 19:24:42Z

0

This is an alternative approach using PostgreSQL arrays

create table responses (
    id serial not null  unique,
    x   integer,
    created timestamp default now()
);

 create table accessory (
    id serial not null,
    responses_id integer references responses (id),
    sensor_id  integer,
    value text,
    created timestamp default now()
);

insert into responses  (x) values  ( 1), (2),(3),(4);

insert into accessory  (responses_id ,  sensor_id, value ) values  
( 1, 1, 'A' ), ( 1, 2, 'Ab' ),  ( 1, 3, 'Ac' ),  ( 1, 4, 'Ad' ),
( 2, 4, 'Ab' ), ( 1, 2, 'bAb' ),  ( 3, 3, 'bAc' ),  ( 4, 4, 'bAd' );

select *, array(
    select value
    from accessory
    where accessory.responses_id  = responses.id  
    order by  accessory.created
    ) as accessory_values
from responses;

Query result includes an array column with all the accessory values that match response.id

edited May 10, 2013 at 19:24

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

answered May 10, 2013 at 18:13

Tim Child

3,0221 gold badge28 silver badges25 bronze badges

4 Comments

Tom Over a year ago

That looks like a really good alternative. I forgot you can do that in Postgres. I suppose I can do aggregation inside the subselect as well.

Tom Over a year ago

For some reason the join isn't working in the sub-select. If I hard-code the value of the response table id I get data, but when I join I get an empty array. This is what the SQL looks like:

SELECT created,  array(select sensor_id from results_accessoryvalue where response_id = results_flattenedresponse.id) FROM results_flattenedresponse

Tim Child Over a year ago

I think is because select sensor_id from results_accessoryvalue where response_id = results_flattenedresponse.id this code references two tables but only 1 is in the from statement

Tom Over a year ago

I'm not clear on what you mean: there's only one table in your inner and outer from statements. What am I doing differently?

Collectives™ on Stack Overflow

SQL: Turning Rows into Columns for Variable Number of Rows

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related