Problem source：http://bbs.csdn.net/topics/390991336 .
Table 1 (T1) stores single-value id field and name field. A selection of the source data is as follows:
Table 2 (T2) stores multi-value ids field as follows:
According to T1’s id field and name field, we want to transform T2’s ids field into a field with values being the corresponding names. The transformed T2 is expected to be like this:
A1，A2：Execute SQL statements to retrieve data from T1 and T2.
Of course, the above approach is based on the assumption that values of id are consecutive integers. If they are not, then the last step could be rephrased as follows:
The SQL approach uses subquery to generate distinct group numbers, and then solves the problem with relatively complex JOIN. The following shows how it is coded in MySQL:
select group_concat(t1.name) names from(
select @row:=@row+1 as gid,ids from t2,(SELECT @row :=0 ) r
) t2 left join t1 on find_in_set(t1.id,t2.ids) group by t2.gid
But note that this SQL approach can only apply in the situation where there are no duplicate values for ids in T2. Otherwise only one of the duplicates is allowed to be kept. For instance, the transformed result of “2,1,2,3” is “a,b,c” instead of “b,a,b,c”. By comparison, esProc syntax is more intuitive, easier to understand and can be used more widely.