CODEWITHSUNDEEP
postgresqlcrosstab
Mistu4u
Mistu4u

Reputation: 5416

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

Upvotes: 0

Views: 368

Answers (2)

Malvin Patrick
Malvin Patrick

Reputation: 55

Yes, you need to add order by. I had same issue like @Mistu4u, and after add 'order by', my query become true. I think it's because because the mechanism of crosstab looking the ordering of data (the 'row_name' column)

Upvotes: 1

Mistu4u
Mistu4u

Reputation: 5416

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.

Upvotes: 4

Related Questions