Conversion of procedure making crosstab to function

Hello Everybody,
I have the following problem. I found procedure alolowing me to create
dynamic crosstab and it works fine, but I cannot save the results as a
table. Is there any way to do this? Maybe someone is in possesion of
function which works as below procedure? Or someone is able to change
this procedure to function which returns table which could be used with
command CREATE TABLE?
Procedure looks as follows:
CREATE PROCEDURE sp_TRANSFORM
/*
Purpose: Creates a Pivot(tm) table for the specified table,
view or select statement
Author: svenh@.itrain.de
Version: 1.1
History: march 2000 version 1.0
july 2002 version 1.1
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 = 0
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_Column 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_Column AS VARCHAR(255))) +
N') ELSE @.TransformPart + '', ' + @.Aggregate_Function +
N' (CASE CAST(' + QUOTENAME(@.Pivot_Column) +
N' AS nVARCHAR(255)) WHEN ''' + CAST(' +
QUOTENAME(CAST(@.Pivot_Column As VarChar(255))) +
N' AS nVARCHAR(255)) + ''' THEN ' +
@.Aggregate_Column +
N' ELSE 0 END) AS '' + QUOTENAME(' +
QUOTENAME(CAST(@.Pivot_Column AS VARCHAR(255))) +
N') END FROM (SELECT DISTINCT ' +
QUOTENAME(CAST(@.Pivot_Column 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)
END
GO
Thank you very much for any help,
Rafal
*** Sent via Developersdex http://www.examnotes.net ***You can try something like this...
INSERT INTO TableName
EXEC proc_name
For example
CREATE TABLE #HelpDB(name VARCHAR(255), db_size VARCHAR(255), owner
VARCHAR(255), dbid INT, created VARCHAR(255), status VARCHAR(255),
compatibility_level VARCHAR(255))
INSERT INTO #HelpDB
EXEC sp_helpdb
SELECT *
FROM #HelpDB
DROP TABLE #HelpDB
"Rafal Ba" <ash@.robertjanowski.pl> wrote in message
news:ONdDCvbmFHA.2904@.tk2msftngp13.phx.gbl...
> Hello Everybody,
> I have the following problem. I found procedure alolowing me to create
> dynamic crosstab and it works fine, but I cannot save the results as a
> table. Is there any way to do this? Maybe someone is in possesion of
> function which works as below procedure? Or someone is able to change
> this procedure to function which returns table which could be used with
> command CREATE TABLE?
> Procedure looks as follows:
> CREATE PROCEDURE sp_TRANSFORM
> /*
> Purpose: Creates a Pivot(tm) table for the specified table,
> view or select statement
> Author: svenh@.itrain.de
> Version: 1.1
> History: march 2000 version 1.0
> july 2002 version 1.1
> 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 = 0
> 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_Column 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_Column AS VARCHAR(255))) +
> N') ELSE @.TransformPart + '', ' + @.Aggregate_Function +
> N' (CASE CAST(' + QUOTENAME(@.Pivot_Column) +
> N' AS nVARCHAR(255)) WHEN ''' + CAST(' +
> QUOTENAME(CAST(@.Pivot_Column As VarChar(255))) +
> N' AS nVARCHAR(255)) + ''' THEN ' +
> @.Aggregate_Column +
> N' ELSE 0 END) AS '' + QUOTENAME(' +
> QUOTENAME(CAST(@.Pivot_Column AS VARCHAR(255))) +
> N') END FROM (SELECT DISTINCT ' +
> QUOTENAME(CAST(@.Pivot_Column 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)
> END
> GO
>
> Thank you very much for any help,
> Rafal
>
> *** Sent via Developersdex http://www.examnotes.net ***|||Unfortunately, I can't make new table because number of her columns is
not constant in time. Probably, there is in all crosstables.
I have solved this problem as follows:
1) Create View from procedure using OPENROWSET
CREATE VIEW MyView AS
SELECT *
FROM OPENROWSET('SQLOLEDB','seattle1';'manage
r';'MyPass',
'EXEC MyProcedure')
2) Create table from view
SELECT * INTO NewTable FROM MyView
Maybe somebody has other idea?
*** Sent via Developersdex http://www.examnotes.net ***