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 @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
>