>>Hii Naomi, see my Query is this,Its giving amount fine
>>
>>declare @DueDate as datetime,@Branch_code as int,@class_id as int,@yearid as int
>>set @Branch_code=1 set @class_id=29 set @yearid=11 set @Duedate=(SELECT MAX(DueDate) FROM ClassFeeStructureTable WHERE SchoolID = @Branch_code AND ClassId = @class_id)
>>
>>create table #amttable(id int identity(1,1),EnrolmentId nvarchar(max),StudentId int,FeeTypeId int,FeeName nvarchar(50),Amount float,Amount1 nvarchar(max),Concession float,BillNumber int,FeeStatus nvarchar(50),Name nvarchar(max),IsBlock int)
>>insert into #amttable EXEC GetFeeStatus @yearid,@Branch_code,@class_id, @Duedate
>>DECLARE @SQL NVARCHAR(max),@SQL1 NVARCHAR(max)
>>
>>SELECT @SQL1 = (
>>SELECT ' + COALESCE(MAX(case when FeeName = ' + quotename(FeeName, '''') +
>>' then Amount end),0)
>>- COALESCE(MAX(case when FeeName = ' + quotename(FeeName, '''') +
>>' then Concession end),0) '
>>FROM (
>>SELECT DISTINCT FeeName
>>FROM #amttable
>>) S
>>ORDER BY FeeName
>>FOR XML PATH('')
>>,type
>>).value('.', 'nvarchar(max)')
>>
>>SELECT @SQL1 = SUBSTRING(LTRIM(@SQL1),2,LEN(@SQL1))
>>SELECT @SQL = (
>>SELECT ', COALESCE(MAX(case when FeeName = ' + quotename(FeeName, '''') +
>>' then Amount end),0) AS ' + quotename(FeeName) + '
>>, COALESCE(MAX(case when FeeName = ' + quotename(FeeName, '''') +
>>' then Concession end),0) AS [Concession] '
>>FROM (
>>SELECT DISTINCT FeeName,FeeTypeId
>>FROM #amttable
>>) S
>>ORDER BY FeeTypeId
>>FOR XML PATH('')
>>,type
>>).value('.', 'nvarchar(max)')
>>
>>SELECT @SQL = 'SELECT EnrolmentId, StudentId, [Name] ' + @SQL + ', MAX(BillNumber) As BillNumber,'+@SQL1+' As TotalBillAmount from #amttable
>>GROUP BY EnrolmentId, StudentId, [Name]
>>ORDER BY [Name]'
>>
>>PRINT @SQL
>>
>>EXECUTE (@SQL)
>>
>>drop table #amttable
>>
>>
>>
>>But when i update my query
>>
>>SELECT @SQL = 'SELECT EnrolmentId, StudentId, [Name],feestatus ' + @SQL + ', MAX(BillNumber) As BillNumber,'+@SQL1+' As TotalBillAmount from #amttable
>>GROUP BY EnrolmentId, StudentId, [Name],feestatus
>>ORDER BY [Name]'
>>
>>Its retreiving single record two or three times, Actually some students has two or three Feestatus,the problem is coming with only these Students..I am attaching image also..
>>Plz help
>
>So, how do you want to resolve the rows for the students having different FeeStatus? Do you want to sum amounts for both statuses? Or pick up a row for particular FeeStatus in case where are several Fee Statuses?


*************************************
I wants to take the amount which is greater than 0 i.e see my case will always be like

Emty ,# ,Dollar

Amount greater than 0 will always be only with one of this Three Means

Emty # Dollar
0 0 450

670 0 0