SQL Server: column with list of foreign keys

Question

SQL Server problem:

I have a table with a column containing a list of foreign keys

| ID | PRICE | LIST_OF_FOREIGN_IDS |
------------------------------------
| 3 | 89 | 67,68,69 |
| 4 | 120 | 45,46 |

I need a view with a single ID and a single FOREIGN_ID per row.

| ID | PRICE | FOREIGN_ID |
---------------------------
| 3 | 89 | 67 |
| 3 | 89 | 68 |
| 3 | 89 | 69 |
| 4 | 120 | 45 |
| 4 | 120 | 46 |

Does anybody know a solution to get the data like the one from the second table?

This is terrible database design. Is it too late to go back and break `list_of_foreign_ids` into a single value column? Putting multiple values in a single column like this will make your life more painful than it needs to be. — Abe Miessler, Jun 03 '13 at 20:56
Use a [split function like this](http://stackoverflow.com/a/2507408/119477) and join on the results. — Conrad Frix, Jun 03 '13 at 21:01
http://stackoverflow.com/questions/5123585/how-to-split-a-single-column-values-to-multiple-column-values — Yelko, Jun 03 '13 at 21:02

score 6 · Answer 1 · answered Jun 03 '13 at 21:01

If possible you need to fix your database design on this table so you are not storing a comma separate list of values. This will be very difficult to maintain.

Ideally your table structure could be altered something like the following:

create table item_details
(
  id int,
  price int
);

create table foreign_details
(
  id int,
  details varchar(50)
);

create table item_foreign
(
  item_id int,
  foreign_id int
);

Then you would get the result using a query:

select i.id, i.price, f.id 
from item_details i
inner join item_foreign ifd
  on i.id = ifd.item_id
inner join foreign_details fd
  on ifd.foreign_id = f.id

If you cannot fix your current table structure, then you can use a split function to separate the data into multiple rows. A sample function could be:

CREATE FUNCTION [dbo].[Split](@String varchar(MAX), @Delimiter char(1))       
returns @temptable TABLE (items varchar(MAX))       
as       
begin      
    declare @idx int       
    declare @slice varchar(8000)       

    select @idx = 1       
        if len(@String)<1 or @String is null  return       

    while @idx!= 0       
    begin       
        set @idx = charindex(@Delimiter,@String)       
        if @idx!=0       
            set @slice = left(@String,@idx - 1)       
        else       
            set @slice = @String       

        if(len(@slice)>0)  
            insert into @temptable(Items) values(@slice)       

        set @String = right(@String,len(@String) - @idx)       
        if len(@String) = 0 break       
    end   
return 
end;

Then you will use CROSS APPLY to pass in the comma separated list for each row similar to this:

select t.id, t.price,
  c.items foreign_id
from yt t
cross apply dbo.split(t.LIST_OF_FOREIGN_IDS, ',') c;

See SQL Fiddle with Demo

score 0 · Answer 2 · answered Jun 03 '13 at 21:08

If these are foreign key ids, then you have another table that contains all of them. The following is not a particularly efficient way of doing it, but it avoids having to define another function.

select t.id, t.price, ft.foreign_id 
from t join
     foreigntable ft
     on ','+LIST_OF_FOREIGN_IDS +',' like '%,'+cast(ft.foreign_id as varchar(255))+',%'

This is using the like method for finding things in a string. It adds delimiteds onto each end. A search for "1" is really a search for ",1,", so it does not match "10".

You can readily create the view as:

create myview as
    select t.id, t.price, ft.foreign_id 
    from t join
         foreigntable ft
         on ','+LIST_OF_FOREIGN_IDS +',' like '%,'+cast(ft.foreign_id as varchar(255))+',%'

SQL Server: column with list of foreign keys

2 Answers2