помогите с crosstab

gda

Дата: 29.12.2014 22:31:06

ontology-prod=> select sub_name, document_id, result
  from ontology.vw_doc_with_result t
 where t.document_id in (3930179,3930177,3930178) order by sub_name, document_id;
           sub_name           | document_id | result
------------------------------+-------------+--------
 DEALER Amenities             |     3930177 | 96.3
 DESIGN - Amenties Packages   |     3930178 | 97.5
 PRODUCT - Inquiry - Negative |     3930179 | 92.9
(3 rows)

ontology-prod=> select *
ontology-prod-> from crosstab(
ontology-prod(> 'select sub_name, document_id, result
ontology-prod'>   from ontology.vw_doc_with_result t
ontology-prod'>  where t.document_id in (3930179,3930177,3930178) order by sub_name, document_id')
ontology-prod-> as vw_doc_with_result(sub_name text, "c3930177" text, "c3930178" text, "c3930179" text);
           sub_name           | c3930177 | c3930178 | c3930179
------------------------------+----------+----------+----------
 DEALER Amenities             | 96.3     |          |
 DESIGN - Amenties Packages   | 97.5     |          |
 PRODUCT - Inquiry - Negative | 92.9     |          |
(3 rows)

ontology-prod=>

а надо получить, что не так в запросе

           sub_name           | c3930177 | c3930178 | c3930179
------------------------------+----------+----------+----------
 DEALER Amenities             | 96.3     |          |
 DESIGN - Amenties Packages   |          | 97.5     |
 PRODUCT - Inquiry - Negative |          |          | 92.9

Добрый Э - Эх

Дата: 30.12.2014 04:01:05

gda,

сделай через CASE и не мучайся.

gda

Дата: 30.12.2014 09:09:10

CASE не катит так как данных может быть больше, document_id могут быть другие, и не только 3 document_id, значения в sub_name - может дублироваться с разными значениями в "result" для одного и того же document_id

лопата

Дата: 02.01.2015 09:06:43

gda,

BEGIN;
create extension  tablefunc;
create table t as 
WITH t (sub_name,document_id, result) AS (values
 ('DEALER Amenities'::text,		3930177::int, 96.3::double precision)
 ,('DESIGN - Amenties Packages',	3930178 , 97.5)
 ,('PRODUCT - Inquiry - Negative',	3930179 , 92.9)
 )
SELECT * FROM t 
;

 select *
from crosstab(
'select sub_name, ''c''||document_id::text, result
from t
order by 1, 2'
,'SELECT ''c''||document_id::text FROM t')
as vw_doc_with_result(sub_name text, c3930177 double precision, c3930178 double precision, c3930179 double precision);
--------------
'DEALER Amenities',96.3,,
'DESIGN - Amenties Packages',,97.5,
'PRODUCT - Inquiry - Negative',,,92.9