Do not build queries in the code. Do it on server side. For example, you can create stored procedure, like:
CREATE PROCEDURE CreateMyPivot
@class NVARCHAR(30),
@exam NVARCHAR(30)
AS
BEGIN
DECLARE @cols NVARCHAR(300)
DECLARE @dt NVARCHAR(2000)
DECLARE @pt NVARCHAR(4000)
SET @cols = '[Paper1],[Paper2],[Paper3],[Paper4],[Paper5],[Paper6],[Paper7]'
SET @dt = 'SELECT Regno, Sname, class FROM stdAdmission WHERE Class=' + @class
SET @pt = 'SELECT Regno, Sname, class, ' + @cols + ' ' +
'FROM (' + @dt + ') AS DT ' +
'PIVOT (MAX([AgregateField]) FOR class IN (' + @cols + ')) AS PT'
EXEC(@pt)
END
How to execute stored procedure from VB.NET?
How to: Execute a Stored Procedure that Returns Rows[
^]
Using Stored Procedures with a Command[
^]