Syntax Error when trying to use Dynamic Pivot Query

I’ve been playing around with the PIVOT function for a while. I have a table that looks like this

IdPersona IdEstadoSocio Periodo
-------------------------------
1044659   6             2021-06
721396    5             2021-06
219886    6             2021-06
1906611   7             2021-06
1027906   2             2021-06

Every ClientID is repeated once for each month. Every month new rows are added to the table with a new period associated to the row. It’s an incrementing table.

What I need to do is to PIVOT the table, so that it basically ends up like this:

IdPersona 2021-01 2021-02 2021-03 2021-04 
----------------------------------------
1044659  6       3       1       4
721396   5       5       2       6 
219886   6       6       4       1
1906611  7       7       9       2 
1027906  2       1       1       1

Naturally, I want my code to be dynamic. I don’t want to harcode every month like:

    SELECT *
FROM [fid].[FACT_Socios_Resumen_Periodo]
PIVOT(MAX(IdEstadoSocio) 
      FOR [Periodo] IN ([2021-01], [2021-02], [2021-03], [2021-04], [2021-05], [2021-06])) AS PVTTable

So I’ve been trying several dynamic solutions found in here, but none of them are working for me, and I don’t know why. And it’s driving me crazy.

This solutions gives me

Incorrect Syntax near ‘FOR’

My code below:

DECLARE @cols AS NVARCHAR(MAX),@query  AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT DISTINCT ',' + QUOTENAME(periodo) 
            FROM [fid].[FACT_Socios_Resumen_Periodo]
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT IdPersona, ' + @cols + ' from 
            (
                select IdPersona
                    , IdEstadoSocio
                    , periodo
                from [fid].[FACT_Socios_Resumen_Periodo]
           ) x
            pivot 
            (
                 max(IdEstadoSocio)
                for periodo in (' + @cols + ')
            ) p '

execute(@query)

The second solution provided in the same link gives me

A variable that has been assigned in a SELECT statement cannot be included in a expression or assignment when used in conjunction with a from clause.

Which is kinda understandable, as the query tries to solve getting the DISTINCT values of [Period] in a recursive way, as far as I understood. However, everybody accepted that as a viable answer too.

My code below:

DECLARE @cols  AS NVARCHAR(MAX)='';
DECLARE @query AS NVARCHAR(MAX)='';

SELECT @cols = @cols + QUOTENAME(periodo) + ',' FROM (select distinct periodo from [fid].[FACT_Socios_Resumen_Periodo] ) as tmp
select @cols = substring(@cols, 0, len(@cols)) --trim "," at end

set @query = 
'SELECT * from 
(
    select idpersona, idestadosocio, periodo from [fid].[FACT_Socios_Resumen_Periodo]
) src
pivot 
(
    max(idestadosocio) for periodo in (' + @cols + ')
) piv'

execute(@query)

The third solution I tried gives me the same error noted above, with a similar syntax but not exactly the same. It tries to solve the DISTINCT [Period]s recursively.

My code below:

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)

--Get distinct values of the PIVOT Column 
SELECT @ColumnName = ISNULL(@ColumnName + ',', '') + QUOTENAME(periodo)
FROM (SELECT DISTINCT periodo FROM [fid].[FACT_Socios_Resumen_Periodo]) AS Periodos
 
--Prepare the PIVOT query using the dynamic 
SET @DynamicPivotQuery = 
  N'SELECT IdPersona, ' + @ColumnName + '
    FROM [fid].[FACT_Socios_Resumen_Periodo]
    PIVOT(MAX(IdEstadoSocio) 
          FOR periodo IN (' + @ColumnName + ')) AS PVTTable'
--Execute the Dynamic Pivot Query
EXEC sp_executesql @DynamicPivotQuery

So, what exactly am I doing wrong?? Can anybody throw me a hint? I’m failing over solutions approved by the whole community, and I can’t understand why. I basically tried to understand the code, copied it, and replaced columns and tables. Just that. I didn’t change anything more. Thanks in advance!

Answer

Looking at your third solution.

First thing I notice is that you are not coalescing the initial null value of @ColumnName when you do your concatenation.

SELECT @ColumnName = Isnull(@ColumnName + ',', '') + QUOTENAME(periodo)
FROM (SELECT DISTINCT periodo FROM [fid].[FACT_Socios_Resumen_Periodo]) AS Periodos

That should solve your problem.

If you would PRINT the result @ColumnName and @DynamicPivotQuery, before you execute it, it will usually show you where the problems are.

For versions of sql that do not support SELECT @Variable

SQL Azure and SQL DW only support using SET when setting variable values. You can use STRING_AGG() on these servers

set @ColumnName = (select string_agg(QUOTENAME(periodo),',') from (select distinct periodo from FACT_Socios_Resumen_Periodo) t)

Advertisement

Answer