mardi 30 juin 2015

Stored procdure null value convert into zero and can't using order by clause

This query return:

month 2014 2015  

apr   null  2000

feb   3000  null

dec   null  2000

may   5000  null

How to solve Null value convert into zero and Showing all month by order

This is my query:

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

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(year(DateOfTransaction)) 
                    from TBL_Transactionmaster
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT [Month],' + @cols + ' from 
             (
                SELECT 
            left(datename(month,DateOfTransaction),3)as [month], year(DateOfTransaction) as [year]
            ,  Amount 
        FROM TBL_Transactionmaster
            ) x
            pivot 
            (
                sum(amount)
                for [year] in (' + @cols + ')
            ) p '

execute(@query)

Aucun commentaire:

Enregistrer un commentaire