Actualmente estoy moviendo una base de datos MS Access a SQL Server. Tengo un cross-tab query que necesito convertir a un Pivot table.

Pivot table estará en un sproc que proporciona parámetros para month y year. Esos parámetros se colocan en la cláusula WHERE de un subquery.

SQL hasta ahora:

SELECT IDNbr, [Name]
FROM (SELECT a1.IDNbr , a2.[CustName] as [Name] , a1.BalDate, a1.Balance 

    FROM IDTable a1 INNER JOIN CustTable a2 ON (a1.IDNbr = a2.IDNbr)

    WHERE MONTH(a1.BalDate) = @month AND YEAR(a1.BalDate) = @year) as d1

PIVOT (
    SUM(Balance)
    For Balance in ([BalDate]) --Error: see below
 ) piv;

El error que recibo en la sección Pivot es:

El nombre de columna "BalDate" especificado en el operador PIVOT entra en conflicto con el nombre de columna existente en el argumento PIVOT.

Y también recibo el error:

La columna 'BalDate' se especificó varias veces para 'piv'.

Datos actuales:

Customer  |   BalDate   | Balance
----------+-------------+--------
Customer1 |  1/01/2017  | 0.00
Customer1 |  1/02/2017  | 0.00
Customer1 |  1/03/2017  | 0.00
Customer1 |  1/04/2017  | 0.00
....      |  ....       | ....

Datos deseados:

|   Customer   |   01/01/2017  |   01/02/2017  |    01/03/2017  |   01/04/2017  |    01/05/2017  |   01/06/2017  |    01/07/2017  |   01/08/2017  |    01/09/2017  |    01/10/2017  | ....
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|    Customer1 |     0.00      |      0.00     |      0.00      |     0.00      |      0.00      |     0.00      |      0.00      |      0.00     |      0.00      |      0.00      | ....
|    Customer2 |     0.00      |      0.00     |      0.00      |     0.00      |      0.00      |     0.00      |      0.00      |      0.00     |      0.00      |      0.00      | ....
|    Customer3 |     0.00      |      0.00     |      0.00      |     0.00      |      0.00      |     0.00      |      0.00      |      0.00     |      0.00      |      0.00      | ....

Pregunta principal : ¿Cómo haría para corregir los errores en mi Pivot Table?

Pregunta secundaria : ¿Cómo podría obtener Current data a Desired data?

Todavía soy un principiante en SQL y SQL Server, así que me disculpo si la respuesta es obvia.

Gracias de antemano por cualquier ayuda y consejo! ¡Y con mucho gusto trataré de aclarar cualquier cosa que parezca confusa o poco clara!


Publicaciones a las que hice referencia e intenté aplicarlas a mi situación:

SQL Pivot Multiple Columns

Nombre de columna especificado en los conflictos del operador pivote

La columna se especificó varias veces

Cómo crear una consulta de tabla cruzada

Datos de columna de pivote de SQL Server

0
Symon 7 sep. 2018 a las 19:10

3 respuestas

La mejor respuesta

Puede intentar usar SQL dinámico para BalDate.

Debido a que su consulta tiene un parámetro para que pueda intentar usar sp_executesql y agrega parámetros en la sintaxis de ejecución.

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

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(a1.BalDate) 
            FROM IDTable a1
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')



set @query = 'SELECT *
FROM (
    SELECT a1.IDNbr , a2.[CustName] as [Name] , a1.BalDate, a1.Balance 
    FROM IDTable a1 INNER JOIN CustTable a2 ON (a1.IDNbr = a2.IDNbr)
    WHERE MONTH(a1.BalDate) = @month AND YEAR(a1.BalDate) = @year
) as d1
PIVOT (
    SUM(Balance)
    For Balance in ('+ @cols +') --Error: see below
) piv'


EXECUTE sp_executesql @query, N'@year INT,@month INT', 
                    @year = @year,
                    @month = @month
1
D-Shih 7 sep. 2018 a las 16:43

Parece que necesitas un pivote dinámico,

Primero debe seleccionar todas las fechas que se utilizarán en el pivote. Las fechas deben estar en formato QuoteName, que es [Fecha]; de lo contrario, el pivote no lo entenderá.

DECLARE @Days NVarchar(MAX)
SEt @Days = STUFF((SELECT ',' + QUOTENAME(a1.BalDate) 
                    FROM IDTable a1
                    group by a1.BalDate
                    order by a1.BalDate
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')
Select @Days

Luego tienes que construir un pivote dinámico, algo como esto.

DECLARE @FinalQuery NVARCHAR (MAX)
SET @FinalQuery =   'SELECT *
                     INTO #ToReportOn
                     FROM
                         (SELECT a1.IDNbr , a2.[CustName] as [Name] , a1.BalDate, a1.Balance 
                          FROM IDTable a1 INNER JOIN CustTable a2 ON (a1.IDNbr = a2.IDNbr)
                          WHERE MONTH(a1.BalDate) = @month AND YEAR(a1.BalDate) = @year) as d1
                         ) A
            PIVOT
                  (
                    SUM(Balance)
                   For BalDate in ('+@Days +')
                  ) B
ORDER BY document_group

SELECT * FROM #ToReportOn 
'
--EXECUTE(@FinalQuery)
PRINT @FinalQuery

No probé esto. Pero creo que el pivote dinámico es el enfoque para resolver su problema.

1
Markov 7 sep. 2018 a las 16:44

En MS Sql Server necesitará un sql dinámico para esto.

Primero calcule una variable con las fechas.
Luego utilícelo en la cadena para la consulta dinámica.

Puede probarlo aquí en rextester

DECLARE @Dates NVARCHAR(max);

SELECT @Dates = CONCAT(@Dates + ', ', QUOTENAME(BalDate)) 
FROM IDTable
WHERE YEAR(BalDate) = @year
  AND MONTH(BalDate) = @month 
GROUP BY BalDate
ORDER BY BalDate;

DECLARE @DynSql NVARCHAR(max); 
SET @DynSql = 'SELECT *
FROM
(
  SELECT a1.IDNbr, a2.[CustName] as [Name], a1.BalDate, a1.Balance 
  FROM IDTable a1 
  INNER JOIN CustTable a2 ON (a1.IDNbr = a2.IDNbr)
  WHERE MONTH(a1.BalDate) = @month 
  AND YEAR(a1.BalDate) = @year
) as src
PIVOT (
  SUM(Balance) 
  FOR BalDate IN ('+ @Dates +') 
) pvt';

DECLARE @Params NVARCHAR(500) = N'@year INT, @month INT';  

-- SELECT @DynSql AS DynSql;
EXECUTE sp_executesql @DynSql, @Params, @year = @year, @month = @month;
1
LukStorms 8 sep. 2018 a las 19:02