postgresqlcrosstab

Why is Crosstab query returning multiple rows instead of one?

I have written a query like the following:

select * from (select *  from crosstab ('select event_item_id, attribute_id, nullif(attribute_value,'''') from event_plan.event_item_attribute_value ',    $$values('size'),('height') ,('floatattr') ,('currencyattr') ,('date_attr') ,('floattwice') $$) as  final_result(event_item_id uuid, size text ,height text ,floatattr text ,currencyattr text ,date_attr text ,floattwice text))    transpose where transpose.event_item_id = '43323dba-d3bf-4f14-a4e0-e55a162864c8';

This query is returning 3 rows instead of only one row which I am expecting. What am I doing wrong here?

Result

Solution

  • I added order by event_item_id to the existing crosstab query and then it worked. Although, I am still not sure how it worked. So now my query is 

    select * from (select * from crosstab ('select  event_item_id, attribute_id, attribute_value from event_plan.event_item_attribute_value 
              order by event_item_id', $$values('size'),('height'),('floatattr'),('currencyattr'),('date_attr'),('floattwice') $$) as final_result(event_item_id uuid, size text ,height text ,floatattr text ,currencyattr text ,date_attr text ,floattwice text)) transpose where transpose.event_item_id = '43323dba-d3bf-4f14-a4e0-e55a162864c8';

    I would appreciate though if someone can explain me how that order by clause changed the result of the query.