PIVOT VIEW using PostgreSQL

@Erwin: It said "too long by 7128 characters" for a comment! Anyway:

Your post gave me the hints for the right direction, so thank you very much, but particularly in my case I need it be truly dynamic. Currently I've got 38886 rows with 49 different items (= columns to be pivoted).

To first answer yours and @Jasen's urgent question: The source table layout is not up to me, I'm already very happy to get this data into an RDBMS. If it were to me, I'd always save UTC-timestamps! But there's also a reason for having the data saved as strings: it may contain various data types, like boolean, integer, float, string etc.

To avoid confusing me further I created a new demo dataset, prefixing the data type (I know some hate this!) to avoid problems with keywords and changing the timestamp (--> minutes) for better overview:

-- -------------------------------------------------------------------------- -- Create demo table of given schema and insert arbitrary data -- -------------------------------------------------------------------------- DROP TABLE IF EXISTS table_source; CREATE TABLE table_source ( column_id BIGINT NOT NULL, column_name CHARACTER VARYING(255), column_timestamp TIMESTAMP WITHOUT TIME ZONE, column_value CHARACTER VARYING(32672), CONSTRAINT table_source_pkey PRIMARY KEY (column_id) ); INSERT INTO table_source VALUES ( 15,'sensor2','2015-01-03 22:01:05.872','88.4'); INSERT INTO table_source VALUES ( 16,'foo27' ,'2015-01-03 22:02:10.887','-3.755'); INSERT INTO table_source VALUES ( 17,'sensor1','2015-01-03 22:02:10.887','1.1704'); INSERT INTO table_source VALUES ( 18,'foo27' ,'2015-01-03 22:03:50.825','-1.4'); INSERT INTO table_source VALUES ( 19,'bar_18','2015-01-03 22:04:50.833','545.43'); INSERT INTO table_source VALUES ( 20,'foo27' ,'2015-01-03 22:05:50.935','-2.87'); INSERT INTO table_source VALUES ( 21,'seNSor3','2015-01-03 22:06:51.044','6.56'); SELECT * FROM table_source; 

Furthermore based on @Erwin's suggestions I created a view which already converts the data type. This has the nice feature, beside being fast, to only add required transformations for known items, but not impacting other (new) items.

-- -------------------------------------------------------------------------- -- Create view to process source data -- -------------------------------------------------------------------------- DROP VIEW IF EXISTS view_source_processed; CREATE VIEW view_source_processed AS SELECT column_timestamp, column_name, CASE LOWER( column_name) WHEN LOWER( 'sensor3') THEN CAST( column_value AS DOUBLE PRECISION) * 1000.0 ELSE CAST( column_value AS DOUBLE PRECISION) END AS column_value FROM table_source ; SELECT * FROM view_source_processed ORDER BY column_timestamp DESC LIMIT 100; 

This is the desired result of the whole question:

-- -------------------------------------------------------------------------- -- Desired result: -- -------------------------------------------------------------------------- /* | column_timestamp | bar_18 | foo27 | sensor1 | sensor2 | seNSor3 | |---------------------------+---------+---------+---------+---------+---------| | "2015-01-03 22:01:05.872" | | | | 88.4 | | | "2015-01-03 22:02:10.887" | | -3.755 | 1.1704 | | | | "2015-01-03 22:03:50.825" | | -1.4 | | | | | "2015-01-03 22:04:50.833" | 545.43 | | | | | | "2015-01-03 22:05:50.935" | | -2.87 | | | | | "2015-01-03 22:06:51.044" | | | | | 6560 | */ 

This is @Erwin's solution, adopted to the new demo source data. It's perfect, as long as the items (= columns to be pivoted) doesn't change:

-- -------------------------------------------------------------------------- -- Solution by Erwin, modified for changed demo dataset: -- http://stackoverflow.com/a/27773730 -- -------------------------------------------------------------------------- SELECT * FROM crosstab( $$ SELECT column_timestamp, column_name, column_value FROM view_source_processed ORDER BY 1, 2 $$ , $$ SELECT UNNEST( '{bar_18,foo27,sensor1,sensor2,seNSor3}'::text[]) $$ ) AS ( column_timestamp timestamp, bar_18 DOUBLE PRECISION, foo27 DOUBLE PRECISION, sensor1 DOUBLE PRECISION, sensor2 DOUBLE PRECISION, seNSor3 DOUBLE PRECISION ) ; 

When reading through the links @Erwin provided, I found a Dynamic SQL example by @Clodoaldo Neto and remembered, that I had already done it this way in Transact-SQL; this is my attempt:

-- -------------------------------------------------------------------------- -- Dynamic attempt based on: -- http://stackoverflow.com/a/12989297/131874 -- -------------------------------------------------------------------------- DO $DO$ DECLARE list_columns TEXT; BEGIN DROP TABLE IF EXISTS temp_table_pivot; list_columns := ( SELECT string_agg( DISTINCT column_name, ' ' ORDER BY column_name) FROM view_source_processed ); EXECUTE( FORMAT( $format_1$ CREATE TEMP TABLE temp_table_pivot( column_timestamp TIMESTAMP, %1$s ) $format_1$ , ( REPLACE( list_columns, ' ', ' DOUBLE PRECISION, ' ) || ' DOUBLE PRECISION' ) ) ); EXECUTE( FORMAT( $format_2$ INSERT INTO temp_table_pivot SELECT * FROM crosstab( $crosstab_1$ SELECT column_timestamp, column_name, column_value FROM view_source_processed ORDER BY column_timestamp, column_name $crosstab_1$ , $crosstab_2$ SELECT DISTINCT column_name FROM view_source_processed ORDER BY column_name $crosstab_2$ ) AS ( column_timestamp TIMESTAMP, %1$s ); $format_2$ , REPLACE( list_columns, ' ', ' DOUBLE PRECISION, ') || ' DOUBLE PRECISION' ) ); END; $DO$; SELECT * FROM temp_table_pivot ORDER BY column_timestamp DESC LIMIT 100; 

Beside getting this into a stored procedure, I will, for performance reasons, try to adopt this to an intermediate table where only new values are inserted. I'll keep you up-to-date!

Thanks!!!

L.

PS: NO, I don't want to answer my own question, but the "comment"-field is too small!