Pivote por Año

[former_member203638]

Estoy haciendo este pivote para que me cuente cuantas facturas por año por cliente pero el resultado me saca cero cuando le pongo year(T0.DocDate) as Year, si le pongo month no hay problema.

SELECT Distinct P.Code, P.Name,
[1] as '2011',
[2] as '2012',
[3] as '2013'
FROM
(SELECT Distinct 
T0.CardCode as Code, T0.CardName as Name,t0.DocEntry,
year(T0.docdate) as year
FROM dbo.OINV T0 
group by T0.CardCode,T0.CardName,T0.DocEntry,T0.DocDate
) X 
PIVOT (count(x.DocEntry) 
FOR year IN
([1],[2],[3])
) P

Alguna sugerencia?

Saludos.

Alessandro.

[felipe_loyolarodriguez · Accepted solution]

Pivoteas por los alias

Saludos

[former_member203638 · Accepted solution]

Gracias por tu comentario Felipe, ya habia probado como me comentas y ya he probado ambos ejemplos y este es el resultado que me da.

[felipe_loyolarodriguez · Accepted solution]

Eso es porque tienes los años como varchar, deben ser numéricos, así:

SELECT DISTINCT P.Code, P.Name,  
[2011] as [2011],  
[2012] as [2012],  
[2013] as [2013]  
FROM  
(
SELECT Distinct  
T0.CardCode as Code, T0.CardName as Name,t0.DocEntry,  
year(T0.docdate) as year  
FROM dbo.OINV T0  
) X  
PIVOT (count(x.DocEntry)  
FOR year IN  
([2011],[2012],[2013])  
) P  

Saludos

FLR

[former_member203638 · Accepted solution]

Gracias Felipe, el alias me estaba mareando.

Saludos

Alessandro.

[Former Member]

Que tal Alessandro!

Les comparto mi query que utilizo, es con SQL dinámico:

-- DECLARACIÓN DE VARIABLES
DECLARE @Cad VARCHAR(MAX), @SQL NVARCHAR(MAX)
-- INICIALIZACIÓN DE VARIEBLES
SET @Cad = ','
-- CONCATENAR AÑOS DE FACTURAS
SELECT @Cad = @Cad + ',[' + Años.Año + ']'
FROM (SELECT DISTINCT CONVERT(VARCHAR,YEAR(DocDate)) [Año] FROM OINV) [Años]
-- QUITAR COMAS AL PRINCIPIO
SELECT @Cad = REPLACE(@Cad,',,','')
-- CONSULTA GENERAL
SET @SQL= N'
SELECT * FROM
(
SELECT CardCode, CardName, YEAR(DocDate) [Año], DocEntry
FROM OINV
) [T0]
PIVOT (
          COUNT(T0.DocEntry) FOR T0.[Año] IN (' + @Cad + ')
)AS [Count]
'
-- EJECUTAR SQL DINÁMICO
EXECUTE SP_EXECUTESQL @SQL

Igual creo que se puede optimizar más.

Saludos.

Carlos.

[former_member203638]

Gracias Carlos, era bronca del alias.

Saludos.

Alessandro.

[felipe_loyolarodriguez]

Alessandro, es al revés

SELECT Distinct P.Code, P.Name,
'2011' as [1],
'2012' as [2],
'2013' as [3]
FROM
(SELECT Distinct
T0.CardCode as Code, T0.CardName as Name,t0.DocEntry,
year(T0.docdate) as year
FROM dbo.OINV T0
group by T0.CardCode,T0.CardName,T0.DocEntry,T0.DocDate
) X
PIVOT (count(x.DocEntry)
FOR year IN
([1],[2],[3])
) P

o de lo contrario, sería así

SELECT Distinct P.Code, P.Name,
'2011' as [2011],
'2012' as [2012],
'2013' as [2013]
FROM
(SELECT Distinct
T0.CardCode as Code, T0.CardName as Name,t0.DocEntry,
year(T0.docdate) as year
FROM dbo.OINV T0
group by T0.CardCode,T0.CardName,T0.DocEntry,T0.DocDate
) X
PIVOT (count(x.DocEntry)
FOR year IN
([2011],[2012],[2013])
) P

Saludos

Felipe Loyola