Column values separated by comma

Question

I have 3 tables in SQL Server Table1, Table2 and Table3 where one column in Table1 has comma-separated PK values of the other two tables.

Now I want to separate the values from the column of Table1 and use that to select the data from other two tables according to the values found.

database is not designed mby me .... but now we have come to stage where we cant change it — user987166, Jun 23 '12 at 09:52
You *could* (1) add two columns `FKTb12` and `FKTbl2` to your table, (2) extract that data **once** from that comma-separated columns into those `FKTb12` and `FKTbl2` columns, (3) establish proper FK constraints to `Table2` and `Table3` from `FKTb12` and `FKTbl2` columns, and then (4) **ditch that crappy comma-separated column** all together! Pretty easy, really... — marc_s, Jun 23 '12 at 11:19
@marc_s: Agree with you entirely, this is indeed very easy. But perhaps the OP meant that the problem is not merely about converting the CSV column into a set of columns. It may so happen that some code, already existing and, perhaps, used in production, needs to be changed as well. I'm not justifying the situation in general, my point is merely that it may not be that easy. — Andriy M, Jun 23 '12 at 11:27

score 0 · Answer 1 · answered Jun 23 '12 at 09:50

Try this

Select Table1PkValue,Table2PKValue
From
(
    Select SubString(PKColumn, 0,CharIndex(PKColumn, ',')) As    
    Table1PkValue,SubString(PKColumn, 
    CharIndex(PKColumn,',')+1,Len(PKColumn)-CharIndex(PKColumn,',')+1) As Table2PKValue
    From Table1
 )
As T
INNER JOIN Table2 ON Table2.PkColumn=T.Table1PkValue
INNER JOIN Table3 ON Table3.PKColumn=T.Table2KValue

Note your PKColumn of Table1 should have comma seperated values. 1st value to the Table2 and second value to the table 3.

Ignore if any syntax errors if it has

Column values separated by comma

1 Answers1