Comma Separated Value and Joins

[Anonymous]

I have a table which has comma separated ID's and another table which is basically a lookup for the ID's. How would I join the two tables?

Table A

PK1 ID1, ID2, ID3

PK2 ID1, ID3

PK3 ID1, ID2

Table B

ID1 Description1

ID2 Description2

ID3 Description3

Thanks,

Dinesh.

[pover · Accepted solution]

Dinesh,

In Table A, create an additional column that will count the number of IDs you have that is

substringcount ( 'ID_List', ',' ) + 1 as No_IDs

Then load a modified Table A in your script to create a one row for each separate ID for each PK through a loop using the "while iterno() " function.

Load PK,
subfield(ID_List,',',iterno()) as ID
Resident TableA
while iterno() <= No_IDs;

That should prepare Table A to be joined with Table B

Regards.

[Anonymous]

Works like a charm. Thank you.