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!
Advertisement
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)