Syntax Error when trying to use Dynamic Pivot Query

Question

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!

Done! My bad. First time posting here in a while, sorry for that — Neko069, Jul 07 '21 at 21:31
I checked three code segments that you have posted and no errors there. I think you have done it correctly. — Gudwlk, Jul 07 '21 at 21:46

JamieD77 · Accepted Answer · 2021-07-08T14:18:32.910

1

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)

edited Jul 08 '21 at 14:18

answered Jul 07 '21 at 21:07

JamieD77

13,796
1
17
27

You're right. But it was just something I forgot to copy in the code above. I tried the solution exactly like the links provided. If you check, you'll see that the original author used the ISNULL for this exact issue you're mentioning. Still, the problem persist, I still get the "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." – Neko069 Jul 07 '21 at 21:11
Also, something to clarify. I'm not able to print the results of both variables, as the query fails before reaching that point. I tried removing the @Columnname + ',' statement, and query does work indeed. However it just lists the first [Period] it finds, and does not 'accumulate' all [Period]s found – Neko069 Jul 07 '21 at 21:17
You're saying that the query is failing before you even get to `EXEC sp_executesql @DynamicPivotQuery`? – JamieD77 Jul 07 '21 at 21:22
1

Your sample images and query don't match up (column names). That makes it more difficult to help if we don't have the correct schema. – JamieD77 Jul 07 '21 at 21:26
Have just updated the post, with accurate columns names and so. What I meant was that even if I comment out the "EXEC sp_executesql @DynamicPivotQuery" and just do a simple SELECT @DynamicPivotQuery, the execution fails anyway, always claiming "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." What I also meant is that if I change the query commenting out the ISNULL(@ColumnName + ',', '') part, the query works. But only provides just one [period] (the one I assume it found first) – Neko069 Jul 07 '21 at 21:36
This is the resultset for what I was explaining IdPersona 2020-11 ----------------------- 955255 4 892681 1 191667 3 1605314 6 857765 7 I'm assuming 2020-11 is the first and only value that the variable @ColumnName stored (because I'm, just for testing purposes, removing that part of the code). But the point is that it works when I comment that ISNULL sentence. So, the error mssg I get makes sense: You cannot recursively call a recently declared variable in a select statement. Right? – Neko069 Jul 07 '21 at 21:41
Sure you can in MS SQL Server. Might be a versioning thing. What version of SQL Server are you using? – JamieD77 Jul 07 '21 at 22:00
2

if you have new version, you might want to use STRING_AGG `set @ColumnName = (select string_agg(QUOTENAME(periodo),',') from (select distinct periodo from FACT_Socios_Resumen_Periodo) t)` – JamieD77 Jul 07 '21 at 22:04
OK THAT DID IT. Might it be something related to my MS version? That worked exactly like I expected. I'm using v18.9.1 for the record. Thanks a ton, man. I'm marking this as a solution – Neko069 Jul 07 '21 at 22:46

Syntax Error when trying to use Dynamic Pivot Query

1 Answers1