Getting multilist from SQL

I had a recent request from a client to generate a SQL export of the structure of a section of the site. One problem I ran into was that I had several multilist controls that reference other items in the site. To report on them, I would need to follow them.

As most of us know, the selected items for a multilist is the item IDs separated by the | character. Using the SQL code below, you will create a function called Split. And then use that in your SQL query to join the with other tables.

CREATE FUNCTION dbo.Split(@String varchar(8000), @Delimiter char(1))     
returns @temptable TABLE (items varchar(8000))     
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

SELECT 
	p.Name as Section
	,c.[Name] as Group
	,t.Name as Product
  FROM [Items] c
  JOIN [Items] p
	ON c.ParentID = p.ID
  JOIN [VersionedFields] f
	ON  c.ID = f.ItemId
  CROSS APPLY Split(f.Value,'|') split
  INNER JOIN Items t ON t.ID =  split.items
  WHERE c.TemplateID = 'F791BCFC-EFBC-4DE8-86B1-A668252582ED'
  AND c.[Name] != '__Standard Values'
  AND f.FieldId = 'B561681C-4197-4613-A121-877D4418E077'
  ORDER BY Section, Group

>