Crosstab of multiple columns in postgresql

Question

I'm trying to combine two crosstab in postgres but stuck it will be great if someone guide me.

-- rainfall CROSS TAB
SELECT * FROM
CROSSTAB(
'SELECT block_id, forecast_date, rainfall_mm
 FROM weather.imd_forecast 
    ORDER BY 1'
)
AS rainfall_crosstab(
    block_id int, rainfall_day_1 numeric, rainfall_day_2 numeric, 
    rainfall_day_3 numeric, rainfall_day_4 numeric, rainfall_day_5 numeric
);
    
-- temp_max CROSS TAB
SELECT * FROM
CROSSTAB(
'SELECT block_id, forecast_date, temp_max_deg_c 
 FROM weather.imd_forecast 
    ORDER BY 1'
)
AS temp_max_crosstab(
    block_id int, temp_max_day_1 numeric, temp_max_day_2 numeric, 
    temp_max_day_3 numeric, temp_max_day_4 numeric, temp_max_day_5 numeric);

Answer 1

Both your queries require ORDER BY 1, 2, not just ORDER BY 1. Else the result can be wrong. See demo in the fiddle.

Combined solution:

SELECT block_id
     , (t1).rainfall_mm AS rainfall_day1, (t1).temp_max_deg_c AS temp_day1
     , (t2).rainfall_mm AS rainfall_day2, (t2).temp_max_deg_c AS temp_day2
     , (t3).rainfall_mm AS rainfall_day3, (t3).temp_max_deg_c AS temp_day3
     , (t4).rainfall_mm AS rainfall_day4, (t4).temp_max_deg_c AS temp_day4
     , (t5).rainfall_mm AS rainfall_day5, (t5).temp_max_deg_c AS temp_day5
FROM   crosstab(
   'SELECT block_id, forecast_date, t
    FROM   weather.imd_forecast t
    ORDER  BY 1, 2'
    ) AS ct (   block_id int, t1 imd_forecast, t2 imd_forecast
           , t3 imd_forecast, t4 imd_forecast, t5 imd_forecast);

fiddle

Read up here:

• PostgreSQL Crosstab Query
• Pivot table with multiple value columns

Answer 2

Assuming block_id and forecast_date are common fields in both crosstab results, you can use a join operation to combine them based on those fields. Based on that assumption, first, we can use common table expressions (CTEs) to create temporary result sets for both the rainfall_crosstab and temp_max_crosstab. Then, we perform a JOIN operation on the block_id and forecast_date fields to combine the two crosstab results.

WITH rainfall_crosstab AS (
    SELECT *
    FROM CROSSTAB(
        'SELECT block_id, forecast_date, rainfall_mm
         FROM weather.imd_forecast 
         ORDER BY 1'
    ) AS rainfall_crosstab(
        block_id int, rainfall_day_1 numeric, rainfall_day_2 numeric, 
        rainfall_day_3 numeric, rainfall_day_4 numeric, rainfall_day_5 numeric
    )
), temp_max_crosstab AS (
    SELECT *
    FROM CROSSTAB(
        'SELECT block_id, forecast_date, temp_max_deg_c 
         FROM weather.imd_forecast 
         ORDER BY 1'
    ) AS temp_max_crosstab(
        block_id int, temp_max_day_1 numeric, temp_max_day_2 numeric, 
        temp_max_day_3 numeric, temp_max_day_4 numeric, temp_max_day_5 numeric
    )
)
SELECT 
    rc.block_id, 
    rc.rainfall_day_1, rc.rainfall_day_2, rc.rainfall_day_3, 
    rc.rainfall_day_4, rc.rainfall_day_5,
    tc.temp_max_day_1, tc.temp_max_day_2, tc.temp_max_day_3, 
    tc.temp_max_day_4, tc.temp_max_day_5
FROM rainfall_crosstab rc
JOIN temp_max_crosstab tc ON rc.block_id = tc.block_id AND rc.forecast_date = tc.forecast_date;