1. Edinson Nongrados
  2. PowerBuilder
  3. Thursday, 15 July 2021 07:51 AM UTC

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

John Fauss Accepted Answer Pending Moderation
  1. Thursday, 15 July 2021 19:12 PM UTC
  2. PowerBuilder
  3. # 1

Hi, Edinson -

I may be missing some key point, but if you are using the PIVOT relational operator in SQL Server's Transact SQL, I'm wondering why you need to utilize a Crosstab presentation style in the DW? I would think you should be able to use a simpler DW style such as Grid or Tabular to display the pivot'ed data produced by the query.

Best regards,
John

Comment
There are no comments made yet.
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Thursday, 15 July 2021 17:37 PM UTC
  2. PowerBuilder
  3. # 2

Hi Edinson;

   Unfortunately, the Crosstab DW style is not supported within the SyntaxFromSQL() command as a "dynamic" DWO or even using the SQLPreview event to alter the DWO's SQL at run time. The Crosstab DWO style does not like that.

  You certainly can build a static CrossTab DW via the DW Painter using the SQL that you have shown.

Regards ... Chris

Comment
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.