Hi, Good morning.

Is there a way to create a Datawindow with this statement. It really doesn't care if it's embebbed or not.

DB: Sql Server

IDE: PB 2019 R3

I'm doing this because the crosstab which do this, doesn't work and I'm looking for ways to patch the problem meanwhile i'm finding a way to solve the problem with the Crosstab Datawindow.

DECLARE @dml AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)
SELECT @ColumnName = ISNULL(@ColumnName + ',','') + QUOTENAME(text_pregunta)
FROM ( SELECT DISTINCT ( ( convert(char(2),temps_opcions.codi) + '-' + temps_opcions.texte_pregunta ) ) as text_pregunta
FROM calendari_temps, temps_opcions
WHERE ( calendari_temps.codi_entitat = temps_opcions.codi_entitat ) and
( calendari_temps.codi = temps_opcions.codi ) and
( ( calendari_temps.codi_entitat = 'MUSEU' ) AND
( calendari_temps.data_consulta between '01/07/2015 00:00' and '14/07/2021 23:59' ) ) ) as text_pregunta

---- Prepare the PIVOT query using the dynamic
SET @dml =
'SELECT Fecha, Dia,' + @ColumnName + ' FROM
(
SELECT calendari_temps.data_consulta as Fecha,
( case datepart(dw,calendari_temps.data_consulta) when 1 then ''Dl'' when 2 then ''Dm'' when 3 then ''Dx'' when 4 then ''Dj'' when 5 then ''Dv'' when 6 then ''Ds'' else ''Dg'' end) as Dia,
( convert(char(2),temps_opcions.codi) + ''-'' + temps_opcions.texte_pregunta ) as text_pregunta,
( case calendari_temps.opcio_pregunta when 1 then temps_opcions.texte_opcio_1 when 2 then temps_opcions.texte_opcio_2 when 3 then temps_opcions.texte_opcio_3 when 4 then temps_opcions.texte_opcio_4 when 5 then temps_opcions.texte_opcio_5 when 6 then temps_opcions.texte_opcio_6 else '''' end) as texte_opcio
FROM calendari_temps, temps_opcions
WHERE ( calendari_temps.codi_entitat = temps_opcions.codi_entitat ) and
( calendari_temps.codi = temps_opcions.codi ) and
( ( calendari_temps.codi_entitat = ''MUSEU'' ) AND
( calendari_temps.data_consulta between ''01/07/2015 00:00'' and ''14/07/2021 23:59'' ) ) ) AS T
PIVOT (MAX(texte_opcio) FOR text_pregunta IN (' + @ColumnName + ')) AS PVTTable
'

EXEC sp_executesql @dml