Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
martinorth
Starting Member
3 Posts |
 Posted - 2013-06-03 : 16:48:48
|
| 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? |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-06-03 : 17:02:23
|
You need a string splitter. A good one is in this article. Copy the code from Figure 21, run it in an SSMS window to install the function. Then use it like this:SELECT ID, PRICE,Item AS Foreign_IDFROM YourTable y CROSS APPLY dbo.DelimitedSplit8K(y.LIST_OF_FOREIGN_IDS,',') |
 |
|
|
martinorth
Starting Member
3 Posts |
Posted - 2013-06-03 : 18:50:01
|
quote:
Originally posted by James K
You need a string splitter. A good one is in this article. Copy the code from Figure 21, run it in an SSMS window to install the function. Then use it like this:SELECT ID, PRICE,Item AS Foreign_IDFROM YourTable y CROSS APPLY dbo.DelimitedSplit8K(y.LIST_OF_FOREIGN_IDS,',')
Thank you. Your sql works fine for me. I allready had a splitter function SplitInts(). The cross apply was new for me. The query execution time is 5 - 7 seconds. Is there a way to add a nonclustered index? |
|
|
|
|
|
|
|
|
column containing a list of foreign keys