gET lIST CACHE SCRIPT 1

DECLARE @ReportPath varchar(max) = '/Italy/Strade_Province'
--DECLARE @ReportPath varchar(max) = '/Italy/Strade_Region'
DECLARE @ReportDate datetime = dateadd(day,-5,getdate())
declare @ParameterName varchar(50)
declare @ParameterValue varchar(max)
declare @S varchar(max)
declare @R varchar(max)
declare @rw int
CREATE TABLE #tempUncachedReports
(
RowNo int
,ReportName varchar(1000)
,ParameterValue varchar(max)
)
CREATE TABLE #temp
(
 ReportName varchar(1000)
,ParameterName varchar(1000)
,ParameterValue varchar(max)
,RowNo int
)
/*Insert uncached not in report*/
INSERT INTO #tempUncachedReports
SELECT rank() over(order by ItemPath,ParameterValues) as rowNo,ItemPath,ParameterValues
from (
select distinct ItemPath,replace(replace(replace(replace(replace(cast([Parameters] as nvarchar(max)),'&','#'),'%5B','['),'%5D',']'),'%26','&'),'%20',' ') as ParameterValues
from dbo.ExecutionLog3
where Timestart >= @ReportDate
and ItemPath like @ReportPath
--and format = 'RPL'
and Source = 'Live'
) as a

DECLARE @LoopCounter int = 1
DECLARE @MaxVal int = (select max(RowNo) from #tempUncachedReports)
WHILE (@LoopCounter <=@MaxVal)
BEGIN
    SET @R = (select ReportName from #tempUncachedReports where RowNo=@LoopCounter)
 SET @S = (select ParameterValue from #tempUncachedReports where RowNo=@LoopCounter)
 SET @rw = (select RowNo from #tempUncachedReports where RowNo=@LoopCounter)
 while len(@S) > 0
  begin
    set @ParameterName = left(@S, charindex('=', @S+'=')-1)
    set @ParameterValue = replace(left(@S, charindex('#', @S+'#')-1), @ParameterName + '=','')
    set @S = stuff(@S, 1, charindex('#', @S+'#'), '')
   
    insert into #temp
    (
    ReportName
    ,ParameterName
    ,ParameterValue
    ,RowNo
    )
    SELECT @R as ReportName,@ParameterName as ParameterName,@ParameterValue as Parameter,@rw as RowNo
  end
  SET @LoopCounter += 1
END
--Takes into account multiselect parameters
select Main.ReportName, Main.ParameterName, ParameterValue=replace(Left(Main.ParameterValue,Len(Main.ParameterValue)-1),'&amp;','&'),RowNo
from
(
select distinct ReportName, ParameterName,RowNo,
replace(replace((
 SELECT a1.ParameterValue + ',' as ParameterValue
 from #temp a1
 where a1.ReportName = a2.ReportName
 and   a1.ParameterName = a2.ParameterName
 and   a1.RowNo = a2.RowNo
 ORDER BY a1.ParameterName
 FOR XML Path('')
),'<ParameterValue>',''),'</ParameterValue>','') as ParameterValue
from #temp a2
) Main
order by RowNo,ParameterName
--Regular, no multiselect
--select * from #temp
DROP TABLE #temp
DROP TABLE #tempUncachedReports