Fill Future date for null groupby sql(presto)-CodePudding

This might be easier than I'm thinking, but essentially want to fill in values that would be null for ID 2. Example below. Thanks.

Given Table:

|ID| food category | time |
:--:----------:-------
|1 |italian  | 2021-10-01|
|1 | indian  | 2021-10-23|
|1 | american| 2021-10-05|
|1 | mexican | 2021-10-07|
|1 | Chinese | 2021-10-09|
|1 | vietnamese| 2021-10-11|
|1 | thai    | 2021-10-12|
|1 | Moroccan| 2021-9-01|
|1 | russian | 2021-7-01|
|1 | korean  | 2021-4-30|
|1 | canadian| 2021-7-01|
|2 |italian  | 2020-10-11|
|2 | indian  | 2021-04-23|
|2 | american| 2021-10-25|
|2 | mexican | 2021-10-27|

I'd like to transform the table above by grouping by id and food category, but still have the time for ID 2 be replaced with future dates(date_add('year',1,now()) for null time. Since there would be no record for ID 2 for the food categories of Chinese, Vietnamese,thai, moroccan,russian, Korean, and Canadian these would be null, but I'd like them to still show in the group by table and be placed by the date 1 year from now. Example of desired results below. Thank you for the help.

Desired Table:

|ID| food category | time |
:--:----------:-------
|1 |italian  | 2021-10-01|
|1 | indian  | 2021-10-23|
|1 | american| 2021-10-05|
|1 | mexican | 2021-10-07|
|1 | Chinese | 2021-10-09|
|1 | vietnamese| 2021-10-11|
|1 | thai    | 2021-10-12|
|1 | Moroccan| 2021-9-01|
|1 | russian | 2021-7-01|
|1 | korean  | 2021-4-30|
|1 | canadian| 2021-7-01|
|2 |italian  | 2020-10-11|
|2 | indian  | 2021-04-23|
|2 | american| 2021-10-25|
|2 | mexican | 2021-10-27|
|2 | Chinese | 2022-11-23|
|2 | vietnamese| 2022-11-23|
|2 | thai    | 2022-11-23|
|2 | Moroccan| 2022-11-23|
|2 | russian | 2022-11-23|
|2 | korean  | 2022-11-23|
|2 | canadian| 2022-11-23|

CodePudding user response：

you can use following query

SELECT COALESCE(t1.ID,t2.ID) as ID,
       COALESCE(t1.foodcategory,t2.foodcategory) as foodcategory,
       CAST(COALESCE(t2.time,dateadd(year, 1, getdate())) AS DATE) time
FROM
  (SELECT *
  FROM
     (SELECT foodcategory 
     FROM testTB
     GROUP BY foodcategory) t1
     JOIN
     (SELECT id 
     FROM testTB
     GROUP BY id) t2 on 1=1) t1
  LEFT JOIN testTB t2 on t1.ID = t2.ID and t1.foodcategory = t2.foodcategory

WITH cte AS ( 
    select distinct foodcategory from testTB 
    )
SELECT t2.ID,t1.foodcategory,CAST(COALESCE(t3.time,dateadd(year, 1, getdate())) AS DATE) time
FROM cte t1
    FULL OUTER JOIN ( 
    select distinct [ID] from testTB 
    ) t2 on 1=1
    left join testTB t3 on t2.ID = t3.ID and t1.foodcategory = t3.foodcategory
order by t2.id

demo in db<>fiddle

CodePudding user response：

Use a CTE to gather the list of food categories first. Then gather the list of IDs.

WITH cteCat AS ( 
    select distinct [food category] from table 
    )
, cteID AS ( 
    select distinct [ID] from table 
    )

SELECT id.[ID], cat.[food category],
    COALESCE(t.[time], dateadd(year, 1, getdate())) as [time]
FROM cteCat cat
    , cteID id
    LEFT OUTER JOIN table t
        ON t.[ID] = id.[ID]
            AND t.[food category] = cat.[food category]