First of all, using
SELECT *
in pivot table is wrong! Pivot tables need to use at least one column to produce rows and at least one (or more) column that produced column-headers.
More about pivots here:
http://msdn.microsoft.com/en-US/library/ms177410%28v=sql.105%29.aspx[
^]
http://archive.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=PIVOTData[
^]
Secondly, NEVER do it in a code! Use stored procedure on a server side.
More about stored procedures:
http://msdn.microsoft.com/en-us/library/ms190669(v=sql.105).aspx[
^]
http://msdn.microsoft.com/en-us/library/ms187961.aspx[
^]
Probably, your stored procedure should looks like:
CREATE PROCEDURE GetMyPivot()
@class NVARCHAR(20)
AS
BEGIN
DECLARE @cols NVARCAHR(200)
SET @col = STUFF((SELECT DISTINCT '],[' + [FeeType]
FROM FeeMaster1
ORDER BY '],[' + [FeeType]
FOR XML PATH('')),1,2,'') + ']'
SELECT [Regno], [SName], [Class], @cols
FROM (
SELECT [Regno], [FeeType], [SName], [AmountPaid], [Class]
FROM FeeTrans
) AS DT
PIVOT(SUM([AmountPaid]) FOR [FeeType] IN (@cols)) as PT
WHERE [Class]=@class
END