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