Grouping data based on conditions

Question

How would I go about grouping data according to values given in a separate file/table?

For example. I have one column in the table I want to be grouped, called ID, and a separate CSV file with IDs to group the data by. I have not created the CSV file yet, so if You think there is a better way to store these values, feel free to tell. The CSV file could for example look like:

ID1,ID2,ID3;ID4,ID5;ID6,ID7,ID9; ... ;

In this case I would want to group the data by ID1 + ID2 + ID3 and ID4 + ID5 and ID6+ID7+ID9 and so on. The point is, that the groups are consistent, but the data is frequently changed and the tables updated. We are talking of hundreds of groups, so it would be neat to organize the groups into a separate file.

Answer 1

SQL is designed to work with rows and columns, it does not do CSVs very well, especially ones that use multiple delimiter symbols with different meaning.

You should figure out how to present your CSV in the form:

ID,GroupID

where IDs that need to be grouped have the same GroupID. Once that CSV is imported, it will be very easy to group your rows using a join to the imported data:

SELECT
  d.GroupID,
  AGG(...),
  ...
FROM
  YourTable AS t
  INNER JOIN ImportedData AS d ON t.ID = d.ID
GROUP BY
  d.GroupID
;