talal
asked on
MS-SQL CrossTab VIEW
This question has been asked alot.
Howvere there is no clear answers on how to do this.
I read alot of articles about this.
all of wich use Stored Procedure and seem to work fine.
However since i can not execute a stored procedure in a view i can not use the results as a VIEW and link it to another table, etc.
There was one article on the web that came pretty close in allowing me to execute a Stored Procedure in a view using OPENQUERY command and linked server. But it did not work for me.
Here are the articles that i tried:
On Stored Procedures that generate CrossTabs:
https://www.experts-exchange.com/questions/21069502/Reformat-Data-with-a-View-or-Crosstab-Query-or-SP.html
https://www.experts-exchange.com/questions/21068312/COMPLEX-SQL-SERVER-view.html
http://www.winnetmag.com/SQLServer/Article/ArticleID/15608/15608.html
I found an artice that explained how to execute a SP in a view:
SELECT *
FROM OPENQUERY(SEQUEL2,
'EXEC MyDB.dbo.sp_transform @TableOrView_name = ''UV_TotalSpecialChargesYe arSpecific '', @Aggregate_Column = ''SumOfAmount'', @Select_Column = ''OVMPAY'', @pivot_column = ''ODHITN''')
Rowset_1
It game me a long error about "Transaction context in use by another session"
and finally after getting the error message:
Transaction context in use by another session
I looked at this article and did not help;
http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q279857
https://www.experts-exchange.com/questions/20803949/Transaction-context-in-use-by-another-session-Error.html
Any ideas???
Howvere there is no clear answers on how to do this.
I read alot of articles about this.
all of wich use Stored Procedure and seem to work fine.
However since i can not execute a stored procedure in a view i can not use the results as a VIEW and link it to another table, etc.
There was one article on the web that came pretty close in allowing me to execute a Stored Procedure in a view using OPENQUERY command and linked server. But it did not work for me.
Here are the articles that i tried:
On Stored Procedures that generate CrossTabs:
https://www.experts-exchange.com/questions/21069502/Reformat-Data-with-a-View-or-Crosstab-Query-or-SP.html
https://www.experts-exchange.com/questions/21068312/COMPLEX-SQL-SERVER-view.html
http://www.winnetmag.com/SQLServer/Article/ArticleID/15608/15608.html
I found an artice that explained how to execute a SP in a view:
SELECT *
FROM OPENQUERY(SEQUEL2,
'EXEC MyDB.dbo.sp_transform @TableOrView_name = ''UV_TotalSpecialChargesYe
Rowset_1
It game me a long error about "Transaction context in use by another session"
and finally after getting the error message:
Transaction context in use by another session
I looked at this article and did not help;
http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q279857
https://www.experts-exchange.com/questions/20803949/Transaction-context-in-use-by-another-session-Error.html
Any ideas???
What does the stored procedure do? Perhaps a user-defined function can be used instead. Can you post the code for the proc?
ASKER
Sure:
CREATE PROCEDURE sp_TRANSFORM
/*
Purpose: Creates a Pivot(tm) table for the specified table,
view or select statement
Input parameters:
@Aggregate_Function (optional)
the aggregate function to use for the pivot
default function is SUM
@Aggregate_Column
name of column for aggregate
@TableOrView_Name
name of table or view to use
if name contains spaces or other special
characters [] should be used
Can also be a valid SELECT statement
@Select_Column
Column for first column in result table
for this column row values are displayed
@Pivot_Column
Column that is transformed into columns
for this column column values are displayed
@DEBUG
Set this flag to 1 to get debug-information
Example usage:
Table given aTable
content: Product Salesman Sales
P1 Sa 12
P2 Sb 10
P2 Sb 3
P3 Sa 12
P1 Sc 8
P3 Sa 1
P2 Sa NULL
CALL
EXEC sp_Transform 'SUM', 'Sales', 'aTable', 'Product', 'Salesman'
or EXEC sp_Transform @Aggregate_Column='Sales', @TableOrViewName='aTable',
@Select_Column='Product', @Pivot_Column='Salesman'
Result:
Product| Sa | Sb | Sc | Total
-------+----------+------- ---+------ ---+------ ---
P1 | 12,00 | 0,00 | 8,00 | 20,00
P2 | 0,00 | 13,00 | 0,00 | 13,00
P3 | 13,00 | 0,00 | 0,00 | 13,00
-------+----------+------- ---+------ ---+------ ---
Total | 25,00 | 13,00 | 8,00 | 46,00
*/
@Aggregate_Function nvarchar(30) = 'SUM',
@Aggregate_Column nvarchar(255),
@TableOrView_Name nvarchar(255),
@Select_Column nvarchar(255),
@Pivot_Column nvarchar(255),
@DEBUG bit = 1
AS
SET NOCOUNT ON
DECLARE @TransformPart nvarchar(4000)
DECLARE @SQLColRetrieval nvarchar(4000)
DECLARE @SQLSelectIntro nvarchar(4000)
DECLARE @SQLSelectFinal nvarchar(4000)
IF @Aggregate_Function NOT IN ('SUM', 'COUNT', 'MAX', 'MIN', 'AVG', 'STDEV', 'VAR', 'VARP', 'STDEVP')
BEGIN RAISERROR ('Invalid aggregate function: %s', 10, 1, @Aggregate_Function) END
ELSE
BEGIN
SELECT @SQLSelectIntro = 'SELECT CASE WHEN (GROUPING(' +
QUOTENAME(@Select_Column) +
') = 1) THEN ''Total'' ELSE ' +
'CAST( + ' +
QUOTENAME(@Select_Column) +
' AS NVARCHAR(255)) END As ' +
QUOTENAME(@Select_Column) +
', '
IF @DEBUG = 1 PRINT @sqlselectintro
SET @SQLColRetrieval =
N'SELECT @TransformPart = CASE WHEN @TransformPart IS NULL THEN ' +
N'''' + @Aggregate_Function + N'(CASE CAST(' +
QUOTENAME(CAST(@Pivot_Colu mn AS VARCHAR(255))) +
N' AS VARCHAR(255)) WHEN '''''' + CAST(' +
QUOTENAME(@Pivot_Column) +
N' AS NVarchar(255)) + '''''' THEN ' + @Aggregate_Column +
N' ELSE 0 END) AS '' + QUOTENAME(' +
QUOTENAME(CAST(@Pivot_Colu mn AS VARCHAR(255))) +
N') ELSE @TransformPart + '', ' + @Aggregate_Function +
N' (CASE CAST(' + QUOTENAME(@Pivot_Column) +
N' AS nVARCHAR(255)) WHEN '''''' + CAST(' +
QUOTENAME(CAST(@Pivot_Colu mn As VarChar(255))) +
N' AS nVARCHAR(255)) + '''''' THEN ' +
@Aggregate_Column +
N' ELSE 0 END) AS '' + QUOTENAME(' +
QUOTENAME(CAST(@Pivot_Colu mn AS VARCHAR(255))) +
N') END FROM (SELECT DISTINCT ' +
QUOTENAME(CAST(@Pivot_Colu mn AS VARCHAR(255))) +
N' FROM ' + @TableOrView_Name + ') SelInner'
IF @DEBUG = 1 PRINT @SQLColRetrieval
EXEC sp_executesql @SQLColRetrieval,
N'@TransformPart nvarchar(4000) OUTPUT',
@TransformPart OUTPUT
IF @DEBUG = 1 PRINT @TransformPart
SET @SQLSelectFinal =
N', ' + @Aggregate_Function + N'(' +
CAST(@Aggregate_Column As Varchar(255)) +
N') As Total FROM ' + @TableOrView_Name + N' GROUP BY ' +
@Select_Column + N' WITH CUBE'
IF @DEBUG = 1 PRINT @SQLSelectFinal
EXEC (@SQLSelectIntro + @TransformPart + @SQLSelectFinal)
IF @DEBUG = 1 PRINT 'FINAL ----- : ' + (@SQLSelectIntro + @TransformPart + @SQLSelectFinal)
END
GO
CREATE PROCEDURE sp_TRANSFORM
/*
Purpose: Creates a Pivot(tm) table for the specified table,
view or select statement
Input parameters:
@Aggregate_Function (optional)
the aggregate function to use for the pivot
default function is SUM
@Aggregate_Column
name of column for aggregate
@TableOrView_Name
name of table or view to use
if name contains spaces or other special
characters [] should be used
Can also be a valid SELECT statement
@Select_Column
Column for first column in result table
for this column row values are displayed
@Pivot_Column
Column that is transformed into columns
for this column column values are displayed
@DEBUG
Set this flag to 1 to get debug-information
Example usage:
Table given aTable
content: Product Salesman Sales
P1 Sa 12
P2 Sb 10
P2 Sb 3
P3 Sa 12
P1 Sc 8
P3 Sa 1
P2 Sa NULL
CALL
EXEC sp_Transform 'SUM', 'Sales', 'aTable', 'Product', 'Salesman'
or EXEC sp_Transform @Aggregate_Column='Sales',
@Select_Column='Product', @Pivot_Column='Salesman'
Result:
Product| Sa | Sb | Sc | Total
-------+----------+-------
P1 | 12,00 | 0,00 | 8,00 | 20,00
P2 | 0,00 | 13,00 | 0,00 | 13,00
P3 | 13,00 | 0,00 | 0,00 | 13,00
-------+----------+-------
Total | 25,00 | 13,00 | 8,00 | 46,00
*/
@Aggregate_Function nvarchar(30) = 'SUM',
@Aggregate_Column nvarchar(255),
@TableOrView_Name nvarchar(255),
@Select_Column nvarchar(255),
@Pivot_Column nvarchar(255),
@DEBUG bit = 1
AS
SET NOCOUNT ON
DECLARE @TransformPart nvarchar(4000)
DECLARE @SQLColRetrieval nvarchar(4000)
DECLARE @SQLSelectIntro nvarchar(4000)
DECLARE @SQLSelectFinal nvarchar(4000)
IF @Aggregate_Function NOT IN ('SUM', 'COUNT', 'MAX', 'MIN', 'AVG', 'STDEV', 'VAR', 'VARP', 'STDEVP')
BEGIN RAISERROR ('Invalid aggregate function: %s', 10, 1, @Aggregate_Function) END
ELSE
BEGIN
SELECT @SQLSelectIntro = 'SELECT CASE WHEN (GROUPING(' +
QUOTENAME(@Select_Column) +
') = 1) THEN ''Total'' ELSE ' +
'CAST( + ' +
QUOTENAME(@Select_Column) +
' AS NVARCHAR(255)) END As ' +
QUOTENAME(@Select_Column) +
', '
IF @DEBUG = 1 PRINT @sqlselectintro
SET @SQLColRetrieval =
N'SELECT @TransformPart = CASE WHEN @TransformPart IS NULL THEN ' +
N'''' + @Aggregate_Function + N'(CASE CAST(' +
QUOTENAME(CAST(@Pivot_Colu
N' AS VARCHAR(255)) WHEN '''''' + CAST(' +
QUOTENAME(@Pivot_Column) +
N' AS NVarchar(255)) + '''''' THEN ' + @Aggregate_Column +
N' ELSE 0 END) AS '' + QUOTENAME(' +
QUOTENAME(CAST(@Pivot_Colu
N') ELSE @TransformPart + '', ' + @Aggregate_Function +
N' (CASE CAST(' + QUOTENAME(@Pivot_Column) +
N' AS nVARCHAR(255)) WHEN '''''' + CAST(' +
QUOTENAME(CAST(@Pivot_Colu
N' AS nVARCHAR(255)) + '''''' THEN ' +
@Aggregate_Column +
N' ELSE 0 END) AS '' + QUOTENAME(' +
QUOTENAME(CAST(@Pivot_Colu
N') END FROM (SELECT DISTINCT ' +
QUOTENAME(CAST(@Pivot_Colu
N' FROM ' + @TableOrView_Name + ') SelInner'
IF @DEBUG = 1 PRINT @SQLColRetrieval
EXEC sp_executesql @SQLColRetrieval,
N'@TransformPart nvarchar(4000) OUTPUT',
@TransformPart OUTPUT
IF @DEBUG = 1 PRINT @TransformPart
SET @SQLSelectFinal =
N', ' + @Aggregate_Function + N'(' +
CAST(@Aggregate_Column As Varchar(255)) +
N') As Total FROM ' + @TableOrView_Name + N' GROUP BY ' +
@Select_Column + N' WITH CUBE'
IF @DEBUG = 1 PRINT @SQLSelectFinal
EXEC (@SQLSelectIntro + @TransformPart + @SQLSelectFinal)
IF @DEBUG = 1 PRINT 'FINAL ----- : ' + (@SQLSelectIntro + @TransformPart + @SQLSelectFinal)
END
GO
Have you tried running the sp_Transform with @DEBUG=1 and using what is shown after FINAL: ----- to create your view?
ASKER
Yes i did... but it would be the same as just using the SQL statement (with CASE ELSE) which i did not want. In addition the columns are not the same each run.. FOr example more years will be next year .. etc.
I just wanted to know for once and for all if there is a way to get a CROSS TAB View .. or if i can execute a command inside the VIEW.
Thank you,
I just wanted to know for once and for all if there is a way to get a CROSS TAB View .. or if i can execute a command inside the VIEW.
Thank you,
Nope, other than being patient: you should be able to do it in SQL Server 2005, which has PIVOT and UNPIVOT operators.
ASKER
:( .. Someone in this big world may habe found away around this...
Could it be there no one so far really needed a CROSSTAD SQL view?
Any accountant out there?? :p
I have put a full day on this and i would like to see that end with results... I appreciate thoughts on work arounds from anyone.
Could it be there no one so far really needed a CROSSTAD SQL view?
Any accountant out there?? :p
I have put a full day on this and i would like to see that end with results... I appreciate thoughts on work arounds from anyone.
I implemented your proc and a view based on that proc for one of my databases, and it works fine with no error, thus the code itself won't be the problem, i guess.
Can you tell us:
* if SEQUEL2 is a local or remote SQL Server
* what version you have (local and/or remote)
* how the linked server is configured (if at all) etc
CHeers
Can you tell us:
* if SEQUEL2 is a local or remote SQL Server
* what version you have (local and/or remote)
* how the linked server is configured (if at all) etc
CHeers
ASKER
Yes, The fact that other people were able to get this to work got me here.
By the way, It works for me until you try SAVING the view. Were you able to save the view?
SEQUEL2 is a remote server (linking to the same box) Because the OPENQuery command works only with linked server i had to do this.
I am starting to think that this may be a configuration on the server itself.
I have SQL2000 (with latest SPs)
I used the configuration:
Data Access, User Remote Collation, RPC and RPC out are all set to true
Security is "Use the log in security context"
I tried changing around these settings but no luck.
Thank you,
By the way, It works for me until you try SAVING the view. Were you able to save the view?
SEQUEL2 is a remote server (linking to the same box) Because the OPENQuery command works only with linked server i had to do this.
I am starting to think that this may be a configuration on the server itself.
I have SQL2000 (with latest SPs)
I used the configuration:
Data Access, User Remote Collation, RPC and RPC out are all set to true
Security is "Use the log in security context"
I tried changing around these settings but no luck.
Thank you,
ASKER
by the way;
I tried using OPENROWSET instead of OpenQuery...
Same thing.. It opens fine and runs fine when i run the view. BUT it does not save.
same error message:
MSDASQL was unable to begin a distributed transaction.
...... OLE\DB provider returned message ... Transaction context in use by another session
I tried using OPENROWSET instead of OpenQuery...
Same thing.. It opens fine and runs fine when i run the view. BUT it does not save.
same error message:
MSDASQL was unable to begin a distributed transaction.
...... OLE\DB provider returned message ... Transaction context in use by another session
ASKER
Unbelievale!!!!
The fix was not even a thought!.
I was able to fix this problem by creating the view VIA query analyser (not the VIEW interface).
It worked!!!!!!!!!!!!!!!!
:) so THIS is how we do a CROSS TAB view!.. and how we execute a command in a VIEW
not the most efficient but hey!! does the job.
The fix was not even a thought!.
I was able to fix this problem by creating the view VIA query analyser (not the VIEW interface).
It worked!!!!!!!!!!!!!!!!
:) so THIS is how we do a CROSS TAB view!.. and how we execute a command in a VIEW
not the most efficient but hey!! does the job.
ASKER
Angel,, Thank you.. At least you confirmed to me that it worked... That made me put some more time on it.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
:( I did not abandone the question.
I believe i solved my own problem, Thanks Angel has helped, so i will give Angel the points.
Thank you,
I believe i solved my own problem, Thanks Angel has helped, so i will give Angel the points.
Thank you,