Sustituir valores NULL y ordenar columnas por orden cronológico pivot dinámico SQL Server

Question

Buen día, gusto en saludarlos. Ante que todo quiero comentarles que soy un usuario principiante y probablemente, mi consulta no se ajuste a los temas escalados en el foro, así que de antemano pido disculpas por las molestias causadas. Con respecto a mi caso, tengo esta sentencia para generar una tabla pivot dinámica en SQL Server sin embargo, si ven el resultado (ver imagen anexa) las columnas no estan ordenadas en orden cronológico (más antiguo al más reciente) y aparece la palabra NULL en los campos sin información. Necesitó que los campos en NULL se muestre un cero (0) y que la información de las columnas se muestre ordenada del més más antiguo al más reciente. El query que estoy ejecutando es el siguiente:

DECLARE @COLUMN NVARCHAR(MAX) SELECT @COLUMN =ISNULL(@COLUMN + ',','') + QUOTENAME(Mes) FROM (SELECT DISTINCT Mes FROM #temp) AS C EXEC ('SELECT * INTO ##TEMP1 FROM #temp PIVOT (SUM(Monto) FOR [Mes] in ('+@COLUMN+')) as Q') EXEC ('SELECT * into ##temp2 FROM #temp PIVOT (SUM(Monto) FOR [Mes] in ('+@COLUMN+')) as Q')

Select *,"Moneda"= case WHEN [Pais] in ('ARG','CHI','COL','ECU','MX','PER','URU','VEN') THEN 'Euro' END from ##temp1

UNION ALL

Select *,"Moneda"= case WHEN [Pais] in ('ARG','CHI','COL','ECU','MX','PER','URU','VEN') THEN 'Local' END from ##temp2

Answer 1

Hola VÍCTOR DE CAIRES:

La pregunta es perfectamente válida para el foro. El único problema es que no has puesto datos, ni el escenario y hay que responderte recreando lo que has hecho.

Voy a centrarme en el problema, no en como has obtenido los conjuntos dinámicos.

La cabecera tiene el problema.

Has usado correctamente la variable "column", para montar las columnas a pivotar, pero la cabecera requiere otro dinámico, para hacer un isNull(..... 0) as .....

Declare @columnsHeaders nvarchar(max)
 
 SELECT @columnsHeaders = STUFF(
(
SELECT
/* Nota: La siguiente fila tiene que ir en una sola línea. */
   ', ' + 'IsNull('+QUOTENAME(LTRIM(mes))+',0) AS '+QUOTENAME(LTRIM(mes))
/* Fin de Nota */
 FROM
   (
select distinct mes
    FROM #temp
    ) AS T
 ORDER BY
 mes
 FOR XML PATH('')
 ), 1, 1, '');

Ahora tendrías que, al igual que hiciste con la variable columns, también usar la variable columnHeaders

En mi blog, hay una entrada detallada para "Pivot dinámico. Añadir totales", donde puedes observar como se monta, paso a paso.

Answer 2

Hola Javi,

Aprovecho la oportunidad para saludarte y ofrecer disculpas por no haber respondido antes. Ante que todo, te quiero agradecer el tiempo que tomaste en responder a mi consulta. Con respecto a tus consideraciones, adjunto la fuente de los datos para la consulta y la sentencia completa que utilizo generar la tabla pivot. La otra consulta que te quería hacer es si me puedes dar la dirección de tu blog para ver el paso a paso para montar la tabla.

Nuevamente, muchas gracias por tomarte la molestia para contestar mi consulta.

Saludos,

SELECT [Pais],CONVERT (VARCHAR (11),(MES),103) as Mes ,[Modalidad] ,[Segmento]

,cast(Cantidad as decimal (18,0)) as Cantidad ,

cast(Monto as decimal (18,0)) as Monto

,cast(usd as decimal (18,0)) as usd

,[Tipo documento]

,[Negocio]

into #temp

FROM [IMP_DIST].[dbo].[Facturacion_Hispan_new]

ORDER BY [Mes] ASC

DECLARE @COLUMN NVARCHAR(MAX) SELECT @COLUMN =ISNULL(@COLUMN + ',','') + QUOTENAME(Mes) FROM (SELECT DISTINCT Mes FROM #temp) AS C EXEC ('SELECT * INTO ##TEMP1 FROM #temp PIVOT (SUM(Monto) FOR [Mes] in ('+@COLUMN+')) as Q') EXEC ('SELECT * into ##temp2 FROM #temp PIVOT (SUM(Monto) FOR [Mes] in ('+@COLUMN+')) as Q')

Select *,"Moneda"= case WHEN [Pais] in ('ARG','CHI','COL','ECU','MX','PER','URU','VEN') THEN 'Euro' END from ##temp1

UNION ALL

Select *,"Moneda"= case WHEN [Pais] in ('ARG','CHI','COL','ECU','MX','PER','URU','VEN') THEN 'Local' END from ##temp2

Answer 3

Sigo sin entender muchas cosas, aunque te puedo enfocar mucho más a la respuesta que necesitas.

Voy a crear una tabla con las columnas que se ven en las diferentes consultas. No es obligatorio que tengas los mismos tipos de datos, ni tan siquiera muchas filas, porque para el ejemplo creo que es claro.

Fase 1 creo el escenario:

Create table dbo.[Facturacion_Hispan_new]
(	Pais varchar(3),
	Mes datetime,
	Modalidad varchar(10), -- desconocido
	Segmento varchar(10), --desconocido
	Cantidad float,
	Monto float,
	usd float,
	[Tipo Documento] varchar(10),
	Negocio varchar(10)
)
Insert into dbo.[Facturacion_Hispan_new]
( Pais, Mes, Modalidad, Segmento, Cantidad, Monto, usd, [Tipo Documento], Negocio)
values
('ARG', '20231201','MODALIDAD1', 'SEGMENTO1', 100.4, 200, 137, 'Factura', 'Movil'),
('CHI', '20230401','MODALIDAD1', 'SEGMENTO2', 150.7, 100, 175, 'Factura', 'Movil'),
('COL', '20230501','MODALIDAD1', 'SEGMENTO1', 220.3, 400, 199, 'Factura', 'Movil'),
('ARG', '20230701','MODALIDAD1', 'SEGMENTO1', 330.9, 500, 188, 'Factura', 'Movil');

Con la tabla creada y las filas insertadas (a modo de ejemplo). Voy a usar la misma estrategia de una tabla temporal, aunque es bastante probable que no fuera necesario.

Fase 2, una posible solución.

drop table if exists #temp;
SELECT [Pais], 
       CONVERT(VARCHAR(11), (MES), 103) AS Mes, 
       [Modalidad], 
       [Segmento], 
       CAST(Cantidad AS DECIMAL(18, 0)) AS Cantidad, 
       CAST(Monto AS DECIMAL(18, 0)) AS Monto, 
       CAST(usd AS DECIMAL(18, 0)) AS usd, 
       [Tipo documento], 
       [Negocio]
INTO #temp
FROM [dbo].[Facturacion_Hispan_new]
ORDER BY [Mes] ASC;

DECLARE @COLUMN NVARCHAR(MAX);
SELECT @COLUMN = ISNULL(@COLUMN + ',', '') + QUOTENAME(Mes)
FROM
(
    SELECT DISTINCT 
           Mes
    FROM #temp
) AS C;

Declare @columnsHeaders nvarchar(max)
 
SELECT @columnsHeaders = STUFF(
(
SELECT
/* Nota: La siguiente fila tiene que ir en una sola línea. */
   ', ' + 'IsNull('+QUOTENAME(LTRIM(mes))+',0) AS '+QUOTENAME(LTRIM(mes))
/* Fin de Nota */
 FROM
   (
select distinct mes
    FROM #temp
    ) AS T
 ORDER BY
 mes
 FOR XML PATH('')
 ), 1, 1, '');

 DECLARE @QUERY NVARCHAR(4000);
 SET @QUERY = N'
  SELECT
   P.[Tipo Documento],
   P.Negocio,
   P.Pais,
   '+@columnsHeaders + N'
  FROM
  (  
    SELECT T.[Tipo Documento]
         , T.Negocio
         , T.Monto
         , T.PAIS
		 , T.MES
	FROM #temp T
  ) AS T
  PIVOT   
  (
  SUM(MONTO)
  FOR MES IN (' + @COLUMN + N')
  ) AS P;'; 
  
  print @query /* esto te sirve para debugear la consulta */

  exec sp_executeSql @query;

Como puedes observar, los totales ya vienen con valor.

En mi blog tienes más información.