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 @slice="left(@String,@idx" - 1) else 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 1>
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>