I have a table, built from a cron. Lets say it has two columns, Hours and Jobname. eg: Job 1 runs for hours {1,2,3,4} and job 2 runs for hours {3,4,5,6}.
I need to have a query, which ungroups the column hours and list the jobs for each hour.
Expected Output:
Hours Job Name 1 Job 1 2 Job 1 3 Job 1, Job2 4 Job 1, Job2 5 Job 2 6 Job 2
Assuming your jobs column is a comma separated list of data type text (or similar) - separated by ', ' - I convert it to an array with string_to_array() and then unnest() it to separate rows:
SELECT hour, unnest(string_to_array(jobs, ', ')) AS job
FROM tbl
ORDER BY 1;
You could also use regexp_split_to_table(jobs, ', '), which is simpler but does not scale as well with longer strings.
Related questions:
In Postgres 9.3 or later, use a LATERAL join instead. Assuming the query is simple and jobs is defined NOT NULL, this works and typically return jobs according to the original order of elements:
SELECT hour, job
FROM tbl, unnest(string_to_array(jobs, ', ')) job -- implicitly LATERAL
ORDER BY 1;
For more complex queries or if jobs can be NULL:
SELECT hour, job
FROM tbl
LEFT JOIN LATERAL unnest(string_to_array(jobs, ', '))
WITH ORDINALITY AS j(job, ord) ON TRUE
ORDER BY hour, ord;
WITH ORDINALITY requires Postgres 9.4 is only needed to guarantee original order of array elements. Details:
If you have a table of jobs, you can approach this as a join:
select cront.hour, j.name
from cront join
jobs j
on ', '||jobs||', ' like '%, '||j.name||', '
