BigQuery - Resources exceeded during query execution: Not enough resources for query planning - too many subqueries

I'm trying to pivot a table from

~

to

referring to this: Dynamic Pivot Needed with Row_Number(), I tried implementing this by

SELECT DISTINCT ID, Date,
  (select ct.Data from cte ct where ct.ID= cte.ID and ct.Date = cte.Date and ct.rank =1) AS Ghour_1,
  (select ct.Data from cte ct where ct.ID = cte.ID and ct.Date= cte.Date and ct.rank =2) AS Ghour_2,
.
.
.

ct.Date = cte.Date and ct.rank =23) AS Ghour_23,
  (select ct.Data from cte ct where ct.ID = cte.ID and ct.Date = cte.Date and ct.rank =24) AS Ghour_24,
   from cte

but it is throwing resources exceeded error, is this because I have too many subquery select statements? Could anyone please suggest how to optimise this query?

Use below as a starting point

select * from (
  select *, replace(regexp_extract(data, r'\brank:(\d+)$'), ':', '') rank
  from your_table
)
pivot (any_value(data) as rank for rank in ('1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17','18','19','20','21','22','23','24'))    

if applied to sample data in your question - output is

Above is easy to transform to dynamic pivot - see multiple examples here on SO - in particular at least few of my answers dedicated to this