0

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);

enter image description here

3
  • This would be much easier with a table definition (CREATE TABLE statement) , sample data (INSERT), and the desired output. Text, no images. A couple of rows will do. Why are there exactly 5 days in the output? Nov 9, 2023 at 8:09
  • @ErwinBrandstetter thank you for your response, there is exactly 5 days forecast available in my table for each date that's why 5 days. The answer which you shared is not clear for me, could you please elaborate in details with examples or something?
    – SpiderMan
    Nov 9, 2023 at 8:34

2 Answers 2

0

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:

0

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;
1
  • Thank you for your answer
    – SpiderMan
    Nov 10, 2023 at 10:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.