SQLTeam.com | Weblogs | Forums

Problem conversion row to column

sql2008r2

#1

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


#2

Before attempting a solution, I'm thinking - don't you have data to fill in the "grid"?


#3

Cells of grid filling by user.For Default value for all cells can be 1.


#4
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)

#5

Very very thanks


#6

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


#7

Example ? ?


#8

First Apologize for the delay
Second Thank you very much for your attention.
Example:

Script for your effortlessly: :grin:
CREATE TABLE [dbo].[Tb_Student](
[Id] [int] NULL,
[Name] nchar NULL,
[Class] nchar NULL,
[Shift] nchar NULL,
[Score] [float] NULL
) ON [PRIMARY]


#9
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)

#10

Thank you khtan


#11

It Solved............


#12

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?


#13

try execute your procedure in query window, verify the result there first


#14

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)`


#16

What hapend?


#17

post withdrawn by author (you)


#18

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... :weary:


#19

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


#20

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


#21

select ',''0''AS ' + quotename(Sec_Name)