hi all,
I have a table named Tbl_Sections.
I want a special output(with procedure).
This is important that number of records and data of table is variable.So,My procedures should be dynamic.
For another information please see attachment file...
please help me.
thanks
Before attempting a solution, I'm thinking - don't you have data to fill in the "grid"?
Cells of grid filling by user.For Default value for all cells can be 1.
declare @cols varchar(max),
@sql varchar(max)
select @cols = stuff((select ',''''AS ' + quotename(Sec_Name)
from Tbl_Sections
where Sec_Base = 1
for xml path('')), 1, 1, '')
print @cols
select @sql = 'select Sec_Name,' + @cols
+ 'from Tbl_Sections where Sec_Base = 0 order by Sec_Name'
print @sql
exec (@sql)Very very thanks
Forgive me,
If we have another field in Tbl_Sections 'field3' named and we want Sum(field3) value for all cells of grid,
in this case your query what will change?
Thanks
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)