Select columns based on a CSV in another table

Question

I have a table which contains the following:

Table1

ID Range        Rate
1  A,B,C,D,E,F   1.2
2  A,B,C         3.1

and another table:

Table2

ID A B C D E F G H J K
1  1 2 1 3 4 2 4 5 8 1     
2  1 2 1 3 4 2 4 5 8 1

Basically this tells us which columns we can apply the rate to, e.g we can apply the rate 1.2 to values that are stored in columns A,B,C,D,E,F of table2 and rate of 3.2 should be applied to columns A,B and C only.

I am joining the two table on the ID column

Select * From Table1 
Inner Join Table2 ON Table1.ID = Table2.ID

But what I am trying to achieve is after joining the 2 tables to select the columns from Table2 based on the contents of the Range column of Table1.

Based on the above example, from the Table1 the first column's range field has: A,B,C,D,E,F so from Table2, I am trying to select only columns A,B,C,D,E,F and apply the rate (1.2) to all of them and leave the rest of the columns untouched, so the solution will look like this:

ID A       B     C      D      E      F      G  H   J  K
1  1*1.2  2*1.2  1*1.2  3*1.2  4*1.2  2*1.2  4  5   8  1
2  1*3.1  2*3.1  1*3.1  3      4      2      4  5   8  1

Hope this makes sense.

Thanks

and you are trying to do this in a single SQL select statement? — paul
These tables should almost certainly be normalised. Difficult to say for sure without knowing anything about the data. — David Aldridge
Does not have to be in a single SQL and unfortunately normalization is not an option at the moment. — 03Usr
Where does the result data go? I'd personally only attempt this using SQL or PL/SQL if it was my last option. — paul

Gordon Linoff Gordon Linoff · Accepted Answer · 2013-05-24T13:15:03

You are looking for a substring in a comma delimited list. A SQL standard way to do this is using like:

select t2.id,
       t2.a * (case when ','||Range||',' like '%,A,%' then t1.rate else 1 else end) as a,
       t2.b * (case when ','||Range||',' like '%,B,%' then t1.rate else 1 else end) as b,
       t2.c * (case when ','||Range||',' like '%,C,%' then t1.rate else 1 else end) as c,
       t2.d * (case when ','||Range||',' like '%,D,%' then t1.rate else 1 else end) as d,
       t2.e * (case when ','||Range||',' like '%,E,%' then t1.rate else 1 else end) as e,
       t2.f * (case when ','||Range||',' like '%,F,%' then t1.rate else 1 else end) as f,
       t2.g * (case when ','||Range||',' like '%,G,%' then t1.rate else 1 else end) as g,
       t2.h * (case when ','||Range||',' like '%,H,%' then t1.rate else 1 else end) as h,
       t2.j * (case when ','||Range||',' like '%,J,%' then t1.rate else 1 else end) as j,
       t2.k * (case when ','||Range||',' like '%,K,%' then t1.rate else 1 else end) as k
from Table1 t1 left join
     Table2 t2
     on t1.id = t2.id;

Specifically, this is delimited the range, so each value has a comma before and after (including the first and last ones). It is then looking for a patter for each value.

I should mention that this type of query is an indication of a poor data structure. Your Table1 should have separate rows for each value.

Select columns based on a CSV in another table

2 Answers