Pivot table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Pivot table

Author

Topic

Pinto
Aged Yak Warrior

590 Posts

Posted - 2006-09-08 : 04:51:42

I have read RobVolks article on creating a Pivot table. I have copied his code into a stored procedure. He then explains how to call this code see below. But how do I actually run the EXECUTE statement in my asp.net webpage ?

EXECUTE(crosstab) 'select title from titles inner join sales on (sales.title_id=titles.title_id)
group by title', 'sum(qty)','stor_id','stores'

[url]http://www.sqlteam.com/item.asp?ItemID=2955[/url]

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-09-08 : 15:24:25

You just use a SqlCommand and ExecuteReader or use a SqlDataAdapter to fill a DataSet like you always do.

Put the stored procedue name in the CommandText of the SqlCommand, set the CommandType to CommandType.StoredProcedure, and then add all the parameters.

Here's a C# example with ExecuteReader

SqlConnection myConnection = new SqlConnection(myConnectionString);
SqlCommand myCommand = new SqlCommand("crosstab", myConnection);
myCommand.Parameters.Add("@select", SqlDbType.VarChar, 8000).Value = "select title from titles inner join sales on (sales.title_id=titles.title_id) group by title";
myCommand.Parameters.Add("@sumfunc", SqlDbType.VarChar, 100).Value = "sum(qty)";
myCommand.Parameters.Add("@pivot", SqlDbType.VarChar, 100).Value = "stor_id";
myCommand.Parameters.Add("@table", SqlDbType.VarChar, 100).Value = "stores";
myConnection.Open();
SqlDataReader myReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
while(myReader.Read())
{
//do something with the column values
}
myReader.Close();
myConnection.Close();

Pinto
Aged Yak Warrior

590 Posts

Posted - 2006-09-11 : 05:44:08

I am getting the error message 'Incorrect syntax near the keyword 'END''. Here is how I am calling the sp. It errors on line 'SqlDataAdapter1.Fill(DsOBSummary1, "tblOB_BagsIssued")' It is in asp.net not c#

Dim MySQL As String = "spOB_Crosstab"
Dim MyConn As New SqlConnection(strConn)
Dim Cmd As New SqlCommand(MySQL, MyConn)
Cmd.CommandType = CommandType.StoredProcedure
'
Cmd.Parameters.Add("@select", "select * from tblOB_BagsIssued group by BI_PropertyRef")
Cmd.Parameters.Add("@sumfunc", "Sum(BI_Quantity)")
Cmd.Parameters.Add("@pivot", "BI_PropertyRef")
Cmd.Parameters.Add("@table", "tblOB_BagsIssued")

SqlDataAdapter1.SelectCommand = Cmd
Me.DsOBSummary1.Clear()

SqlDataAdapter1.Fill(DsOBSummary1, "tblOB_BagsIssued")

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-09-11 : 06:03:17

you might want to read jeff's posts about pivoting.
It's better to do it on the client.
here's the list:
http://weblogs.sqlteam.com/jeffs/archive/2005/05.aspx

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-09-11 : 12:09:10

So does this query execute successfully in Query Analyzer?

exec spOB_Crosstab 'select BI_PropertyRef from tblOB_BagsIssued group by BI_PropertyRef', 'Sum(BI_Quantity)', 'BI_PropertyRef', 'tblOB_BagsIssued'

That's what you're sending to SQL Server from your VB .NET code, so try it in QA first, if it doesn't work then you have a problem in the stored procedure.

Pinto
Aged Yak Warrior

590 Posts

Posted - 2006-09-12 : 09:53:39

I am getting this error now when I run it in Query Analyser.....

Server: Msg 8120, Level 16, State 1, Line 1
Column 'tblOB_BagsIssued.BI_Id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'tblOB_BagsIssued.BI_Date' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'tblOB_BagsIssued.BI_Quantity' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-09-12 : 10:05:58

You need to edit the SQL statement that you pass to the cross tab stored procedure so that it is a valid query, just run it on its own first to make sure you have it working.

When you use GROUP BY you cannot select columns that are not in the GROUP BY or in an aggregate function like SUM or COUNT.

So this would work
select BI_PropertyRef from tblOB_BagsIssued group by BI_PropertyRef

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-09-13 : 09:23:25

In third parameter supply all non-aggregate columns used in select query

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources