SQL creating a concatenated string of names from an array of ids

I am developing reports using Streamtask with xReporter. Recently, our databases were updated to allow for multiple projects to be assigned to a single task. To achieve this, a string containing a a set of CSV index values was used to replace the old single index ie: '[1,2,3,4]' instead of 1. As a result we now have to update a slew of reports.

To get around this, I replace the original selection statement:

(select prj.name from project_table prj where prj.id=t.project_id) as project, 

with this line:

(select prj.name from project_table prj where prj.id = ANY((string_to_array(btrim(task_table.projects, '[]'), ','))::integer[])) as project,

which turns the string (successfully) into an array of integer values.

The problem is that I must be able to take this array of indices and retrieve each project name associated with each index, and concatenate them together into a string containing a list of the project names.

Because I have to implement this in an xReporter report, I believe I will need an SQL-only solution to my problem.

Answers


Storing comma separated values in a relation database is a really horrible anti-pattern.

Postgres has some really nifty functions to workaround this design-error which makes the query actually quite easy (but probably not really fast)

select prj.name, tsk.id as task_id
from project_table prj
  join ( 
     select id, regexp_split_to_table(projects, ',')::int as pid
     from task_table
  ) tsk on tsk.pid = prj.id
order by prj.id

You original statement looks like you were really embedding that into a bigger statement, so I'm not sure how suitable the join is. But you could always create a normalized view on the mis-designed table using the above approach (the inner derived table).

Here is a SQLFiddle: http://sqlfiddle.com/#!15/6f9bb/1


Need Your Help

Multiple Render Targets in Metal

objective-c cocoa core-animation render-to-texture metal

I am trying to implement two distinct CAMetalLayers and use one MTLRenderCommandEncoder to render the same scene to both layers (Metal for OS X).

VBA program to convert table to ARFF returns "User-defined type not defined"

vba ms-access ms-access-2007 weka arff

I found this WEKA table to ARFF file converter VBA script. I know nothing about VBA programming and I just want to use this file to help me in my research.