>>>Hiii Thanks for Support. I wants to show fee status which is greater than 0
>>>suppose i have two students and output of two students is coming like
>>>
>>> Emty # Dollar
>>> St1 0 0 450
>>> St2 0 230 0
>>>
>>>
>>>Now St1 has amount greater than 0 in dollar than Dollar Fee status should come with amount 450 same as for St2 # staus with amount 230 shold come
>>
>>As I just answered in MSDN, get rid of FeeStatus column in the list of fields and GROUP BY and the code should work fine as is now since you're using MAX. You can alternatively use SUM, in your case it will be the same.
>********************************************************************************************
>
>Hi Naomi my Final query is this but how to correct it means how to show according to my Requiremnt i don't know I tried alot but not getting result
>
>DECLARE @SQL NVARCHAR(max)
>
>SELECT @SQL = (
>SELECT ', COALESCE(MAX(case when FeeName = ' + quotename(FeeName, '''') +
>' then case when FeeName=''Pending'' and BillNumber=0 then 0 else Amount end end),0) AS ' + quotename(FeeName) + ' ,FeeStatus+'' ''+CAST(COALESCE(MAX(case when FeeName = ' + quotename(FeeName, '''') +
>' then case when FeeName=''Pending'' and BillNumber=0 then 0 else Amount end end),0)as nvarchar(max)) AS ' + quotename(FeeName+'1') + '
>, COALESCE(MAX(case when FeeName = ' + quotename(FeeName, '''') +
>' then Concession end),0) AS [Concession]'
>FROM (
>SELECT DISTINCT FeeName,FeeTypeId
>FROM Sample_Table1
>) S
>ORDER BY FeeTypeId
>FOR XML PATH('')
>,type
>).value('.', 'nvarchar(max)')
>
>
>
>SELECT @SQL = 'SELECT EnrolmentId, StudentId, [Name],Feestatus ' + @SQL + ', MAX(BillNumber) As BillNumber,
>SUM((case when FeeStatus=''$'' or FeeName = ''CREDIT'' THEN -1 ELSE 1 END) * Amount) AS [Total Amount]
> from Sample_Table1 where IsBlock!=1
>GROUP BY EnrolmentId, StudentId, [Name],FeeStatus
>ORDER BY [Name]'
>
>PRINT @SQL
>
>EXECUTE (@SQL)

If you're selecting the FeeStatus as part of your column list and GROUP BY list, you'll get 1 row per each different FeeStatus. If you want to see just one row per student, you need to remove this column.