PIVOT múltiples columnas

PIVOT nos permite convertir filas en columnas. Lo podemos incluir en las instrucciones SELECT, pero sólo se puede realizar a una columna.

Si se intenta realizar una consulta con dos operadores PIVOT y se usa la misma columna en la cláusula FOR se genera el siguiente error: «Invalid column name XXXXXXX».

De igual manera, si se utiliza la misma columna con diferentes ALIAS, se produce un error: «The column name XXXXX specified in PIVOT operator conflicts with the existing column name in the PIVOT argument»

La solución es declarar la misma columna, pero cambiando sus valores, adicionando una constante o concatenando algún indicador y asignando un nuevo ALIAS a la columna.

Ejemplo:

Se requiere mostrar el valor y el número de clientes que facturaron el mes actual y el mes anterior en una línea.

Al realizar la consulta:

SELECT
YEAR(I.IssueDate) [Year],
MONTH(I.IssueDate) [Month],
SUM((I.TotalAmmount / I.Rate)) Amount,
COUNT(I.CustomerID) as Number
FROM  Invoices I with (nolock)
INNER JOIN  InvoiceTypes IT with (nolock) ON (IT.ID = I.TypeID)
AND I.IssueDate > = ‘2011-09-01’
AND I.IssueDate < = ‘2011-10-31’
WHERE I.[IsNull] = 0
AND IT.Code = ‘INVOICE’
AND I.CompanyID = 1
GROUP BY YEAR(I.IssueDate), MONTH(I.IssueDate)

Resultado:

Year      Month    Amount                 Number
2011        9           1358651.99               885
2011        10          1426890.12             875
(2 row(s) affected)

Lo que se quiere es  que los datos de las columnas Amount y Number, se conviertan en Filas, para mostrar los valores y numero de clientes de la facturación de los últimos dos meses:

Actual                 Last                       ActualNumber   LastNumber
1426890.12       1358651.99                875                    885

Para esto, se debe realizar PIVOT a la columna del mes. Se crean las campos [Month] y MonthNumber, los cuales son los mismos datos; sólo que para mostrar los valores se toma el campo [Month] entre valores [0] [1] y para el número de clientes la columna MonthNumber adquiere valores [10] y [11]:

SELECT @CompanyID,
SUM([0]) Actual,
SUM([1]) Last,
SUM([10]) ActualNumber,
SUM([11]) LastedNumber
from
(
SELECT
YEAR(I.IssueDate) [Year],
CASE
WHEN MONTH(I.IssueDate) = MONTH(GETDATE())  THEN 0
ELSE 1
END as [Month],
CASE
WHEN MONTH(I.IssueDate) = MONTH(GETDATE()) THEN 10
ELSE 11
END as MonthNumber,
SUM((I.TotalAmmount / I.Rate)) Amount,
COUNT(I.CustomerID) as Number
FROM  Invoices I with (nolock)
INNER JOIN  InvoiceTypes IT with (nolock) ON (IT.ID = I.TypeID)
AND I.IssueDate > = ‘2011-09-01’
AND I.IssueDate < = ‘2011-10-31’
WHERE I.[IsNull] = 0
AND IT.Code = ‘INVOICE’
AND I.CompanyID = 1
GROUP BY YEAR(I.IssueDate), MONTH(I.IssueDate)
) Main
PIVOT (
MAX(Amount) FOR [Month] IN ([0],[1])
) AS PQ
PIVOT (
MAX(Number) FOR MonthNumber IN ([10],[11])
)  PQ

RESULTADO:

Actual                 Lasted                 ActualNumber LastNumber
———————- ———————- ———— ————
1426890.12      1358651.99             875                       885