Monday, February 07, 2005

Join against Lists in a Column

Now how about you want to select against that Table you now get when you push a list of values through it?

this Second part is very usefull tho you will have to edit the table name within when you change source tables... possibly a sproc????

Declare @CursorLess Table (PkID INT, ItemList INT)

--Non Cursor solution
DECLARE @NC Table (PkID INT, ItemList VARCHAR(2000)) --Cursor like holding bin
DECLARE @PkID AS INT, @ListOfItems As VARCHAR(2000) -- IDs pointing back to original table
INSERT INTO @NC (PkID, ListOfItems)
--Change the below select to the actual PKID and List of Items, and Table
Select PkID, ListOfItems From UserTable --Populate Holding bin
----------------------------------------------------
WHILE EXISTS(SELECT * FROM @NC) --Check that data exists
BEGIN
SELECT TOP 1 @PkID = PkID, @ListOfItems = ListOfItems FROM @NC --Set Variables
INSERT INTO @Cursorless (PkID, ListOfItems) SELECT @PkID, Field1 FROM dbo.udfListToTable(@ListOfItems, ';') -- Populate Temp Table
DELETE From @NC where PkID = @PkID --Clear Variables
END

SELECT * FROM @Cursorless -- Use the final Variable table to join approriately....

No comments:

Post a Comment