CODEWITHSUNDEEP
sqlsql-serverpivot
Thomas
Thomas

Reputation: 34188

Sql server PIVOT query does not return data

i was trying to display data in PIVOT format the below way

Days       2009-APR    2010-APR      2009-DEC    2010-DEC
----------- ----------- -----------  ----------- -----------
1           0           0             0           0
2           0           0             0           0
3           0           0             0           0
4           0           0             0           0
5           0           0             0           0
6           0           0             0           0
7           0           0             0           0
8           0           0             0           0
9           0           0             0           0
30          0           0             0           0
31          0           0             0           0

here is my pivot query which return result but all values are 0. not being able to capture the problem. so please guide me where is the problem.

WITH months(MonthNumber) AS
(
    SELECT 1
    UNION ALL
    SELECT MonthNumber+1 
    FROM months
    WHERE MonthNumber < 31
)
    Select MonthNumber,isnull([2009-APR],0)     
as [2009-APR],isnull([2010-APR],0)     
as [2010-APR],isnull([2009-MAY],0)     
as [2009-MAY],isnull([2010-MAY],0)     
as [2010-MAY],isnull([2009-JUL],0)     
as [2009-JUL],isnull([2010-JUL],0)     
as [2010-JUL],isnull([2009-NOV],0)     
as [2009-NOV],isnull([2010-NOV],0)     
as [2010-NOV],isnull([2009-DEC],0)     
as [2009-DEC],isnull([2010-DEC],0)     
as [2010-DEC] from (    
    select MonthNumber,CAST(YEAR(CurDate) AS VARCHAR)+'-'+CAST(MONTH(CurDate) AS VARCHAR) AS  years,
    isnull(Warranty_Info,0) as Warranty_Info  from EOD_Main    
          right join months on DAY(CurDate ) = MonthNumber   
    ) as xx    
    PIVOT     
    (    
    SUM(Warranty_Info) FOR years IN ([2009-APR],[2010-APR],[2009-MAY],[2010-MAY],[2009-JUL],[2010-JUL],[2009-NOV],[2010-NOV],[2009-DEC],[2010-DEC])    
    )    
    as pvt ORDER BY MonthNumber

this way i check that any data exist for the year 2010 & month December and found that data exist.

   select sum(Warranty_Info) from EOD_Main
    where year(CurDate)=2010 and month(CurDate)=12

so just guide me how to build the pivot query which will return right data for the year & month wise. thanks

FULL SCRIPT

ALTER PROC WarrantyTrendDetails  
(  
 @StYear AS INT,  
 @EdYear  AS INT,  
 @SelectedMonths AS VARCHAR(MAX)  
)  
AS  

--DECLARE @SelectedMonths VARCHAR(MAX)  
DECLARE @TmpSelectedMonths VARCHAR(MAX)  
--SET @SelectedMonths='JAN,FEB,MAR,APR,MAY,JUN,JUL,AUG,SEP,OCT,NOV,DEC'  
--SET @SelectedMonths='APR,MAY,JUL,NOV,DEC'  
SET @TmpSelectedMonths=@SelectedMonths  
--SELECT * FROM StringToTable(@SelectedMonths,',')  

DECLARE @StartYear AS INT,  
        @EndYear  AS INT  

DECLARE @ConCatData VARCHAR(MAX)  
DECLARE @TmpData VARCHAR(MAX)  
DECLARE @years varchar(max), @yearsColumns varchar(max)     
DECLARE @query varchar(max)      



SET @ConCatData=''  

SET @TmpData = ''  
SET @StartYear=@StYear  
SET @EndYear=@EdYear  



WHILE LEN(@TmpSelectedMonths) > 0  
BEGIN  
SET @StartYear=@StYear  
SET @EndYear=@EdYear  

 WHILE( @StartYear <= @EndYear )  
 BEGIN  
  SET @TmpData= LEFT(@TmpSelectedMonths, CHARINDEX(',', @TmpSelectedMonths+',')-1)  
  SET @ConCatData = @ConCatData + (CAST(@StartYear AS VARCHAR)+'-'+@TmpData)+','  
  SET @StartYear=@StartYear+1  
 END  
 SET @TmpSelectedMonths = STUFF(@TmpSelectedMonths, 1, CHARINDEX(',', @TmpSelectedMonths+','), '')  
END  

SET @TmpSelectedMonths=@SelectedMonths  
SET @ConCatData=SUBSTRING (@ConCatData,1,LEN(@ConCatData)-1)  


SELECT @years=COALESCE(@years+',','')   +'['+ cast(data as varchar(8))+']',      
@yearsColumns=COALESCE(@yearsColumns+',','')   +'isnull(['+ cast(data as varchar(8))+'],0)       
as ['+cast(data as varchar(8))+']'      
FROM StringToTable(@ConCatData,',')     

SET @query = 'WITH months(MonthNumber) AS  
(  
    SELECT 1  
    UNION ALL  
    SELECT MonthNumber+1   
    FROM months  
    WHERE MonthNumber < 31  
)  
 Select MonthNumber Days,'+@yearsColumns+' from (      
    select MonthNumber,CAST(YEAR(CurDate) AS VARCHAR)+''-''  
 +CAST(datename(month, CurDate) AS VARCHAR(3)) AS  years,  
 isnull(Warranty_Info,0) as Warranty_Info  from EOD_Main      
          right join months on DAY(CurDate ) = MonthNumber     
    ) as xx      
    PIVOT       
    (      
  SUM(xx.Warranty_Info) FOR years IN ('+@years+')      
    )      
    as pvt ORDER BY Days'      
    --PRINT @query      
    EXEC(@query)      

GO



ALTER PROC [dbo].[WarrantyTrends]    
(    
 @StartYr AS INT,    
 @EndYr  AS INT     
)    
AS    
 DECLARE @query varchar(max)    
 DECLARE @years varchar(max), @yearsColumns varchar(max)    

 SELECT 1 mID, 'January' as month  into #tempMonths   UNION ALL     
    SELECT 2,'February' as month     UNION ALL     
    SELECT 3,'March' as month     UNION ALL     
    SELECT 4,'April' as month     UNION ALL     
    SELECT 5,'May' as month     UNION ALL     
    SELECT 6,'June' as month     UNION ALL     
    SELECT 7,'July' as month     UNION ALL     
    SELECT 8,'August' as month     UNION ALL     
    SELECT 9,'September' as month     UNION ALL     
    SELECT 10,'October' as month    UNION ALL     
    SELECT 11,'November' as month    UNION ALL     
    SELECT 12,'December' as month      


    SELECT @years=COALESCE(@years+',','')   +'['+ cast(years as varchar(4))+']',    
    @yearsColumns=COALESCE(@yearsColumns+',','')   +'isnull(['+ cast(years as varchar(4))+'],0)     
    as ['+cast(years as varchar(4))+']'    
    from  (select distinct YEAR(CurDate) years from EOD_Main    
 WHERE YEAR(CurDate)>=@StartYr AND YEAR(CurDate)<=@EndYr     
 ) as x ORDER BY years  

 SET @query = 'Select months,'+@yearsColumns+' from (    
    select mID, YEAR(CurDate)  years,[MONTH] months,      
    isnull(Warranty_Info,0) as Warranty_Info  from EOD_Main    
          right join #tempMonths on datename(month,CurDate ) =[month]     
    ) as xx    
    PIVOT     
    (    
    SUM(xx.Warranty_Info) FOR years IN ('+@years+')    
    )    
    as pvt ORDER BY mID'    
    PRINT @query    
    EXEC(@query)    

   drop table #tempMonths       

GO

Upvotes: 0

Views: 520

Answers (1)

Taryn
Taryn

Reputation: 247650

There are a few things wrong with your existing query which are causing the result to be wrong.

First, you are creating a string in your subquery and casting columns as a varchar - you need to be sure that your specify the length of the string.

Second, in your subquery you are using the MONTH() function on the CurDate column. The month() function returns an integer not a string - so you are creating a year-month combination that looks like:

years
2009-8
2009-9

This wouldn't normally be an issue but your PIVOT is creating new columns with the names 2009-AUG, etc. Your PIVOT is referencing values that don't exist.

In order to fix this, you will want to use the DATENAME() function instead in your subquery, use:

CAST(datename(month, CurDate) AS VARCHAR(3))

This will create your months with the names AUG, APR, etc. making your full query similar to:

;WITH months(MonthNumber) AS
(
    SELECT 1
    UNION ALL
    SELECT MonthNumber+1 
    FROM months
    WHERE MonthNumber < 31
)
select *
from
(
  select MonthNumber,
    CAST(YEAR(CurDate) AS VARCHAR(4))
    +'-'+UPPER(CAST(datename(month, CurDate) AS VARCHAR(3))) AS  years,
    isnull(Warranty_Info,0) as Warranty_Info 
  from EOD_Main     
  right join months 
    on DAY(CurDate ) = MonthNumber  
) d
PIVOT     
(    
  SUM(Warranty_Info) 
  FOR years IN ([2009-AUG],[2009-SEP])    
) as pvt 
ORDER BY MonthNumber;

See SQL Fiddle with Demo

Upvotes: 4

Related Questions