Example ? ?
First Apologize for the delay
Second Thank you very much for your attention.
Example:
Script for your effortlessly: 
CREATE TABLE [dbo].[Tb_Student](
[Id] [int] NULL,
[Name] nchar NULL,
[Class] nchar NULL,
[Shift] nchar NULL,
[Score] [float] NULL
) ON [PRIMARY]
declare @cols varchar(max),
@sql varchar(max)
select @cols = stuff((select ',' + quotename([Class])
from Tb_Student
group by [Class]
for xml path('')), 1, 1, '')
print @cols
select @sql = 'select * '
+ 'from '
+ '( '
+ ' select Class, Shift, Score from Tb_Student '
+ ') d '
+ 'pivot '
+ '( '
+ ' avg(Score) '
+ ' for [Class] in (' + @cols + ')'
+ ') p '
print @sql
exec (@sql)
1 Like
Thank you khtan
It Solved............
Oh,
I Create a procedure of Query in post 1 and then this procedure I set to Datasource of Gridview in C# but Gridview not any row.
why?
try execute your procedure in query window, verify the result there first
This is Result:
and Codes of Procedure:
`ALTER Procedure [dbo].[Prc_TBMonPerfSecNBase_BlnForm](@P_Year varchar(4),@P_Month varchar(2))
As
Declare @Cols varchar(max),
@Sql varchar(max)
Select @Cols=Stuff((Select ',''''AS ' + quotename(Sec_Name)+',''''AS '+quotename(Sec_Code)
From Tb_Section
Where Sec_Base = 1
For xml path('')), 1, 1, '')
Set @Sql = 'Select Sec_Name,
Sec_Code,'
+@P_Year+'[سال]'+
','
+@P_Month+'[ماه]'+
','
+ @Cols
+ 'From Tb_Section Where Sec_Base = 0 Order by Sec_Name'
exec (@Sql)`
What hapend?
post withdrawn by author (you)
That no important.please see post abover.
Summarized codes:
`ALTER Procedure [dbo].[Teeeest] As
Declare @Cols varchar(max),
@Sql varchar(max)
Set @Cols=stuff((Select ',''''AS ' + quotename(Sec_Name)--+',''''AS '+quotename(Sec_Code)
From Tb_Section
Where Sec_Base = 1
For xml path('')), 1, 1, '');
Set @Sql = 'Select Sec_Name,'
+ @Cols
+ 'From Tb_Section Where Sec_Base = 0 Order by Sec_Name'
exec (@Sql)`
and value of @Col is:
''AS [Sec5],''AS [Sec7],''AS [Sec8],''AS [Sec9],''AS [Sec11]
and output is :
but also my grid no any row... 
if you see result when execute in query window, then your procedure should be fine. Check your front end application code. Sorry can't help you much with that. I am not familiar with that
I have a question,
please see picture of output in above post.If I want Cells of grid have a default value,this is how to?
I go maybe cells has value problem solved.also column 'Sec_Name' have values
select ',''0''AS ' + quotename(Sec_Name)
Ok,problem not solved.thanks alot
Your answer for me not understand
please see:
http://www.c-sharpcorner.com/forums/my-procedure-not-have-output-in-c-sharp-for-datasoure-gridview
I have another question about this problem.
I have this procedure:
ALTER Procedure [dbo].[test3] as Declare @Cols varchar(max), @Sql varchar(max) Set @Cols=('''''AS [a]'); print @cols Set @sql=' select 1 as[one], ' + @Cols exec (@Sql)
This procedure have records in Gridview C# but different with the above code and code below is a 'select' and code below no any record in Gridview C#!!
ALTER Procedure [dbo].[test3] as Declare @Cols varchar(max), @Sql varchar(max) Set @Cols=(Select '''''AS [a]'); print @cols Set @sql=' select 1 as[one], ' + @Cols exec (@Sql)
How to be above Code with 'Pivot'?