Convert rows into Column in Postgress Error-CodePudding

Hello I have created a view, but want to pivot it.

OUTPUT before pivoting:

expected output:

my full query:

SELECT *
FROM CROSSTAB(
  'SELECT DISTINCT GROUP_DEST::TEXT,DEST::TEXT,TIER::TEXT,RATE::TEXT  FROM  VBB_TIER   ORDER BY 1,2')
AS CT(ROW_NAME TEXT, TIER_1 TEXT, TIER_2 TEXT )

I getting this error and unable to resolve:

ERROR:  invalid source data SQL statement
DETAIL:  The provided SQL must return 3 columns: rowid, category, and values.
SQL state: 22023

CodePudding user response：

Using filtered aggregation is typically a lot easier than the somewhat convoluted crosstab() function:

select group_dest, 
       dest, 
       max(rate) filter (where tier in ('0-100', ('0-150')) as tier_1,
       max(rate) filter (where tier in ('101-200', '151-350') as tier_2
from vbb_tier
group by group_dest, dest;