>>Helo to all I have a problem with a Transact sql.. Yesterday i have posted a Problem in social.Msdn forum and i got fine solution by Naomi related to that i have another problem...Plz see my Yesterda's post here is a link
>>
>>http://social.msdn.microsoft.com/Forums/en/transactsql/thread/85d2c2f7-b15f-4863-b8bc-f937f15f4243
>>
>>
>>The last reply(Query) given by Naomi. I make some changes in that query and the final query is this
>>
>>SET ANSI_PADDING ON
>>
>>DECLARE @SQL NVARCHAR(max)
>>SELECT @SQL = (
>> SELECT ',FeeStatus+'' ''+CAST(COALESCE(MAX(case when FeeName = ' + quotename(FeeName, '''') +
>>' then Amount end),0)as nvarchar(max)) AS ' + quotename(FeeName) + '
>> , COALESCE(MAX(case when FeeName = ' + quotename(FeeName, '''') +
>>' then Concession end),0) AS [Concession] ,COALESCE(MAX(case when FeeName = ' + quotename(FeeName, '''') +
>>' then Amount-Concession end),0) as '+quotename(FeeName+'Amt')
>> 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 + ' from Sample_Table1
>> GROUP BY EnrolmentId, StudentId, [Name],FeeStatus
>> ORDER BY [Name]'
>>
>>PRINT @SQL
>>
>>EXECUTE (@SQL)
>>
>>
>>The output is coming like-- you can see image err.jpg
>>
>>The Wrong thing in this output is that Firstly Akshat Gupta is coming two times It should come in a single row and only one time and also Cautionmoney of Akshat Gupta is 2300 its coming in row where Feestatus is empty It should come where Feestatus is $
>>
>>
>>Plz help..If anyone needs some more explaination plz let me Know and also can see the link which i have provided.. In
>>Thanks in advance
>>
>>
>>
>>I am attaching zip file also you just run the script in Your database.. so that you can better catch it
>
>Does Ashkat Gupta have 2 different FeeStatus? Since you're grouping by FeeStatus among other columns, if the person has two (or more) different Fee Statuses, it will have as many rows in the final output.
>
>BTW, why you're converting amount into nvarchar(max)?

****************************************************************

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