Here's a solution that seems to be what you are trying to do...joining one table to another table where the source table has a delimited list of key values in a single column. If this is not what you need, can you post some sample tables and data?
/* Create a test table with a column of delimited values */
CREATE TABLE [dbo].[TestStringInput](
[ID] [int] IDENTITY(1,1) NOT NULL,
[DelimitedStringInput] [VARCHAR](max) NULL,
CONSTRAINT [PK_TestStringInput] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/* Insert some test values */
INSERT INTO dbo.TestStringInput (DelimitedStringInput) VALUES ('234,567,890')
INSERT INTO dbo.TestStringInput (DelimitedStringInput) VALUES ('123,456,789')
INSERT INTO dbo.TestStringInput (DelimitedStringInput) VALUES ('123,567,890')
INSERT INTO dbo.TestStringInput (DelimitedStringInput) VALUES ('123,234,456,789,890')
INSERT INTO dbo.TestStringInput (DelimitedStringInput) VALUES ('890,567,234')
GO
/* Create a test table with dummy data and keys */
CREATE TABLE [dbo].[TestJoinOutPutAlt](
[ID] [int] IDENTITY(1,1) NOT NULL,
[TestValue] [VARCHAR](max) NULL,
CONSTRAINT [PK_TestJoinOutPut] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/* Insert some test values */
SET IDENTITY_INSERT dbo.TestJoinOutPut ON
INSERT INTO dbo.TestJoinOutPut (ID,TestValue) VALUES (123,'George Washington')
INSERT INTO dbo.TestJoinOutPut (ID,TestValue) VALUES (234,'John Adams')
INSERT INTO dbo.TestJoinOutPut (ID,TestValue) VALUES (456,'Thomas Jefferson')
INSERT INTO dbo.TestJoinOutPut (ID,TestValue) VALUES (567,'James Madison')
INSERT INTO dbo.TestJoinOutPut (ID,TestValue) VALUES (789,'James Monroe')
INSERT INTO dbo.TestJoinOutPut (ID,TestValue) VALUES (890,'Andrew Jackson')
SET IDENTITY_INSERT dbo.TestJoinOutPut OFF
GO
/* Create a function to join the delimited values */
/* to the second table's keys as a table */
CREATE FUNCTION [dbo].[TestJoinTable]
(
@strList VARCHAR(50)
)
RETURNS @RtnValue TABLE
(
[ID] INT IDENTITY(1,1)
,[KeyValue] INT
,[TestValue] VARCHAR(MAX)
,PRIMARY KEY (ID)
,UNIQUE (ID)
)
AS
BEGIN
DECLARE
@TestValues VARCHAR(MAX)
,@strXML XML
SET@strXML = CONVERT(XML,'<root><item>' + REPLACE(@strList,',','</item><item>')+'</item></root>')
INSERT INTO @RtnValue (KeyValue, TestValue)
SELECT
Nodes.VALUE
,testjoin.TestValue
FROM
(
SELECT
X.t.value('.','VARCHAR(MAX)') AS Value
FROM
@strXML.nodes('/root/item') X (t)
) AS Nodes
INNER JOIN
dbo.TestJoinOutPut AS testjoin
ON [Value] = testjoin.ID
ORDER BY
testjoin.TestValue--optional order by
RETURN
END
GO
SELECT * FROM dbo.TestStringInput
SELECT * FROM dbo.TestJoinOutPut
/* Output to a table from a single value input */
SELECT * FROM dbo.TestJoinTable('890,567,234')
/* Output to a table by joining to the input table */
SELECT * FROM dbo.TestStringInput AS i
CROSS APPLY dbo.TestJoinTable(i.DelimitedStringInput)