Displaying Rows as Columns

Hello All !
I am using MS-SQL Server.
I have following table :
tblExchangeData
Columns are :
------
1) Trans_Date
2) Sales
3) Purchase
4) Purchase_Brokerage
5) Sales_Brokerage
6) Branch_Name
7) ExchageSegment
There are only two ExchangeSegments : BSE & NSE
I want to calculate brokerage for ExchangeSegments.
(Brokerage=Purchase_Brokerage+Sales_Brokerage)
O/p Should be (Group By Branch):
Branch_Name BSE NSE
------------------
xx 10000 20000
.... so on

Please post the query.
Thanks in advance.

SQL Server have three none standard Aggregate functions that are not Relational Compute Sum, Cube and Rollup. The last two are super agreggate functions. Run a search for all three in the BOL(books online). Try the links below for examples. Hope this helps.

http://www.oreilly.com/catalog/wintrnssql/chapter/ch01.html

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_setu-sus_414d.asp

|||

Here is some source that will allow you to create a SProc and than you pass virtually whatever you want and the Rows Will Become Columns - Some Examples at the end of script!
You know how to configure the data set in MS RS to call a stored Procedure? (With all due respect)
CREATE PROCEDURE spPivot_Table
@.cTable varchar(80),
@.cDown varchar(80),
@.cAcross varchar(80),
@.cFunc varchar(80),
@.cAggFld varchar(80),
@.cWhere varchar(200)
As

Drop Table TempUniq
Drop Table TempPivot

Declare @.cColTtl varchar(80),
@.cSQLStr varchar(200),
@.cSQL varchar(8000),
@.nRows Int,
@.nCntr Int

-- Generate Pivot Key Table
Set @.cSQL = 'Select Distinct'+@.cAcross+' as Pivot_Value Into TempUniq From'+@.cTable+' Where'+@.cWHere+' Order By 1 '
Exec(@.cSQL)
Select IDENTITY(int, 1,1) as Pivot_Row,@.cFunc as Pivot_Func, @.cAggFld as Pivot_AggFld,@.cAcross as Pivot_Fld,@.cColTtl as Pivot_Col,Pivot_Value,@.cSQLStr as Pivot_SQL Into TempPivot From TempUniq Order By Pivot_Value

Update TempPivot Set Pivot_Col = 'Col_'+Replace(RTrim(LTrim(Convert(varchar(80),Pivot_Value))),' ','_')

-- Build and Execute Pivot SQL
Update TempPivot Set Pivot_SQL=LTrim(RTrim(Pivot_Col))+'='+Pivot_Func+'(case when '+Pivot_Fld+'=Pivot_Value and Pivot_Col='''+Pivot_Col+''' Then '+Pivot_AggFld+' else Null end)'
Select @.nRows=Max(Pivot_Row),@.nCntr=Min(Pivot_Row) From TempPivot
Set @.cSQL=''
While @.nCntr <= @.nRows
Begin
Select @.cSQL=@.cSQL+','+Pivot_SQL From TempPivot WherePivot_Row=@.nCntr
Set @.nCntr=@.nCntr+1
End

Set @.cSQL='Select'+@.cDown+','+Substring(@.cSQL,2,8000)+' From'+@.cTable+' Join TempPivot on('+@.cAcross+'=Pivot_Value) Where'+@.cWhere+' Group By'+@.cDown+' Order By'+@.cDown
Exec(@.cSQL)

EXAMPLES OF HOW TO USE -- Just execuate the SP within SQL Query Analyzer These samples use the the Northwind DB -

-- Capabilities
-- Any Combination to "Down" or By Field -- s
-- Functions Available: Sum, Avg, Min, M -- ax, Count, STD, ...
-- Across may be an expression Substring -- (ShipCountry,1,1) = Across A,B,C,D,...-- r>
-- Samples
-- Exec spPivot_Table {Table},{By Fields -- },{Across Colums},{Agg Function},{Pivot -- Field},{Filer}
-- Exec spPivot_Table 'Orders','ShipCoun -- try','Year(OrderDate)','Sum','Freight',' -- 1=1'
-- Exec spPivot_Table 'Orders','ShipCoun -- try','Year(OrderDate)','Sum','Freight',' -- Year(OrderDate)>1996'
-- Exec spPivot_Table 'Orders','Employee -- ID,ShipCountry','Year(OrderDate)','Sum', -- 'Freight','1=1'
-- Exec spPivot_Table 'Orders','Employee -- ID,ShipCountry','Year(OrderDate)','Sum', -- 'Freight','1=1'
-- Exec spPivot_Table 'Orders','Employee -- ID,ShipCountry','Substring(ShipCountry,1 -- ,1)','Sum','Freight','1=1'

|||hi,
can the above store procedure method be used in reporting services...because i need to generate result like the example of displaying rows as columns.|||

Angela:

Yes, I have used the SP many times within Reporting Services.

Try these steps and I think you will get the results you want.

Create the Stored Procedure - CREATE PROCEDURE spPivot_Table You can by the way name this procedure anything you want and I normally name stored procedure the same name as my reporting services report.
(This stored procedure is really executing YOUR SQL statement (Dynamic SQL) and since Dynamic SQL is being executed within the Stored Procedure ensure you eliminate ANY "white space or blanks" in your SQL Statement.
After you create the Stored Procedure open SQL Query Analyzer and construct your SQL statement - once your SQL statement works open a new window in SQL Query Analyzer and then execute the following:
Exec Stored Procedure Name 'YOUR SQL STATEMENT' and within SQL Query Analyzer you should see the results of the Stored Procedure and the cross tab created on the pivot column name. After you get the results you want from the stored procedure and your SQL statement just copy the statement in SQL Query Analyzer and then go to your report in Reporting Services.
In Reporting Services (and your report you are working on) create a data set and when creating the data set Specify COMMAND TYPE as a storedprocedure and in the QUERY STRING "Paste" your statement you copied from SQL Query Analyzer then select OK...
Test or run your data set in Reporting Services and you should see the same results that Reporting Services displays after executing the stored procedure that you would see within SQL Query Analyzer. VOILA! The fields names returned from this stored procedure are different from the names returned within reporting services with a standard SQL statement - but they are obvious once you run the data set for the first time with the pivot table stored procedure.
You can accomplish the same by using a Matrix within your report with reporting services in that the matrix is really doing the pivot table or cross tab for you but I find that it is easier to use the pivot table stored procedure.
Hope this helps!
Best Regards - Joe

|||Also, please look at the post "Limiting Matrix Columns" I provided another solution there as well.........|||Hello !
Yes, this stored procedure is very useful. I have used it many times. And performance is also good.
Thanx Joe|||

Please help. i created the SP in sql 2000 in Northwind Database. This worked. I tried to

run the following : Exec spPivot_Table 'Orders','ShipCoun -- try','Year(OrderDate)','Sum','Freight',' -- Year(OrderDate)>1996'
and below was the result.

Not sure if my synatx above is causing this or if there are other problems too.
Thanks

Server: Msg 3701, Level 11, State 5, Procedure spPivot_Table, Line 11
Cannot drop the table 'TempUniq', because it does not exist in the system catalog.
Server: Msg 3701, Level 11, State 5, Procedure spPivot_Table, Line 12
Cannot drop the table 'TempPivot', because it does not exist in the system catalog.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'Where'.
Server: Msg 208, Level 16, State 1, Procedure spPivot_Table, Line 25
Invalid object name 'TempUniq'.

|||Hello:
Please execute the following statement:
Exec spPivot_Table 'Orders','ShipCountry','Year(OrderDate)','Sum','Freight','Year(OrderDate)>1996'
Your statement contained invalid field names from the table:
run the following : Exec spPivot_Table 'Orders','ShipCoun -- try','Year(OrderDate)','Sum','Freight',' -- Year(OrderDate)>1996'
Please see the BOLD field names above.
The first time you execute the stored procedure - you will get messages that the temp tables are not there - so a message is provided that they can't be deleted because they do not exist. Any further executions will not show those initial messages.
Also, look at the post for "Limiting Matrix Columns - I provided another script that behaves a little differently -
Hope this helps!
Best Regards,
Joe|||Hello:
Please do not take this the wrong way! Please do not use SP as a prefix for creating your stored procedures - this is a Microsoft nameing convention and things may get screwed up in the future. I always prefix my stored procedures with 'EX' for execute.
Best Reagrds,
Joe|||Run it as :
Exec spPivot_Table 'Orders','ShipCountry','Year(OrderDate)','Sum','Freight','Year(OrderDate)>1996'|||There is no problem using an "sp" prefix for a stored procedurename. The issue is with the "sp_" prefix, as SQL Server willfirst try to look in the Master database for such-named storedprocedures. I have recently read an article which explores thisissue, and the author found there is actually a negligible performancepenalty with the "sp_" prefix -- not worth the trouble to go back andrename existing stored procedures.
|||

Thanks, can i create a report using asp.net using the Stored procedure, and if so please

point me in the right direction, allowing a refresh at a push of a button or something like that ??

Thanks

Gavin

|||Hi there,
i have a table result showing like this
Code OrderName
-- ----
123 AAA
111 BBB
and i select some other fields frm another table with column name call
TestResult but i wan to display the result like this;
TestResult Para1 Para2 Para3 Para4
---- -- --- -- ---
1 123 AAA 111 BBB
2 123 AAA 111 BBB
is there anyway of geting something like the above example?
|||You want to display each and every cell as column. But what is use of it?