USE Northwind
EXECUTE sp_Crosstab'SELECT ProductName, Quantity, Discount FROM [Order Details Extended]',NULL,NULL,'Discount','Quantity','SUM'
--------------------------------------------------------------------
Stored Procedure: sp_Crosstab
--------------------------------------------------------------------
USE master
GO
CREATE PROCEDURE dbo.sp_Crosstab
@DBFetch varchar(4000),
@DBWhere varchar(2000) = NULL,
@DBPivot varchar(4000) = NULL,
@DBField varchar(100),
@PCField varchar(100),
@PCBuild varchar( 20),
@PCAdmin varchar( 20) = NULL,
@DBAdmin int = 0,
@DBTable varchar(100) = NULL,
@DBWrite varchar(160) = NULL,
@DBUltra bit = 0
AS
SET NOCOUNT ON
DECLARE @Return int
DECLARE @Retain int
DECLARE @Status int
SET @Status = 0
DECLARE @TPre varchar(10)
DECLARE @TDo3 tinyint
DECLARE @TDo4 tinyint
SET @TPre = 'tbl'
SET @TDo3 = LEN(@TPre)
SET @TDo4 = LEN(@TPre) + 1
DECLARE @DBAE varchar(40)
DECLARE @Task varchar(8000)
DECLARE @Bank varchar(4000)
DECLARE @Cash varchar(2000)
DECLARE @Rich varchar(2000)
DECLARE @DBAI varchar(4000)
DECLARE @DBAO varchar(8000)
DECLARE @DBAU varchar(2000)
DECLARE @Name varchar(100)
DECLARE @Same varchar(100)
DECLARE @Home varchar(160)
DECLARE @Some varchar(20)
DECLARE @Work int
DECLARE @Wink int
SET @DBAE = '##Crosstab' + RIGHT(CONVERT(varchar(10),@@SPID+100000),5)
SET @Task = 'IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE name = ' + CHAR(39) + @DBAE + CHAR(39) + ') DROP TABLE ' + @DBAE
EXECUTE (@Task)
CREATE TABLE #DBAT (Work int IDENTITY(1,1), Name varchar(100))
SET @Bank = @TPre + @DBFetch
IF NOT EXISTS (SELECT * FROM sysobjects WHERE RTRIM(type) = 'U' AND name = @Bank)
BEGIN
SET @Bank = CASE WHEN LEFT(@DBFetch,6) = 'SELECT' THEN '(' + @DBFetch + ')' ELSE @DBFetch END
SET @Bank = REPLACE(@Bank, CHAR(94),CHAR(39))
SET @Bank = REPLACE(@Bank,CHAR(45)+CHAR(45),CHAR(32))
SET @Bank = REPLACE(@Bank,CHAR(47)+CHAR(42),CHAR(32))
END
IF @DBWhere IS NOT NULL
BEGIN
SET @Cash = REPLACE(@DBWhere,'WHERE' ,CHAR(32))
SET @Cash = REPLACE(@Cash, CHAR(94),CHAR(39))
SET @Cash = REPLACE(@Cash,CHAR(45)+CHAR(45),CHAR(32))
SET @Cash = REPLACE(@Cash,CHAR(47)+CHAR(42),CHAR(32))
END
SET @DBField = REPLACE(@DBField,CHAR(32),CHAR(95))
SET @PCField = REPLACE(@PCField,CHAR(32),CHAR(95))
SET @PCBuild = REPLACE(@PCBuild,CHAR(32),CHAR(95))
SET @PCAdmin = REPLACE(@PCAdmin,CHAR(32),CHAR(95))
SET @DBTable = REPLACE(@DBTable,CHAR(32),CHAR(95))
SET @DBWrite = REPLACE(@DBWrite,CHAR(32),CHAR(95))
SET @DBWhere = CASE WHEN @DBWhere IS NULL THEN '' ELSE ' WHERE (' + @Cash + ') AND 0 = 0' END
SET @Some = ISNULL(@PCAdmin,'NA')
SET @Task = 'SELECT * INTO ' + @DBAE + ' FROM ' + @Bank + ' AS T WHERE 0 = 1'
IF @Status = 0 EXECUTE (@Task) SET @Return = @@ERROR
IF @Status = 0 SET @Status = @Return
IF @DBPivot IS NOT NULL
BEGIN
IF LEFT(@DBPivot,6) <> 'SELECT'
BEGIN
SET @Wink = 1
SET @Work = CHARINDEX('',(@DBPivot)+'')
WHILE @Work > 0
BEGIN
SET @Name = SUBSTRING(@DBPivot,@Wink,@Work-@Wink)
INSERT #DBAT (Name) VALUES (@Name)
SET @Wink = @Work + 1
SET @Work = CHARINDEX('',(@DBPivot)+'',@Wink)
END
END
ELSE
BEGIN
SET @Task = 'INSERT #DBAT (Name) ' + @DBPivot
SET @Task = REPLACE(@Task, CHAR(94),CHAR(39))
SET @Task = REPLACE(@Task,CHAR(45)+CHAR(45),CHAR(32))
SET @Task = REPLACE(@Task,CHAR(47)+CHAR(42),CHAR(32))
IF @Status = 0 EXECUTE (@Task) SET @Return = @@ERROR
IF @Status = 0 SET @Status = @Return
END
END
ELSE
BEGIN
SET @Task = ' INSERT #DBAT (Name)'
+ ' SELECT CONVERT(varchar(100),' + @DBField + ')'
+ ' FROM ' + @Bank + ' AS T ' + @DBWhere
+ ' GROUP BY CONVERT(varchar(100),' + @DBField + ')'
+ ' ORDER BY 1'
IF @Status = 0 EXECUTE (@Task) SET @Return = @@ERROR
IF @Status = 0 SET @Status = @Return
END
UPDATE #DBAT SET Name = @Some WHERE Name IS NULL
SET @DBAI = ''
SET @DBAO = ''
SET @Rich = ''
DECLARE Fields CURSOR FAST_FORWARD FOR
SELECT C.name
FROM tempdb.dbo.sysobjects AS O
JOIN tempdb.dbo.syscolumns AS C
ON C.id = O.id
AND C.name != @DBField
AND C.name != @PCField
AND O.name = @DBAE
ORDER BY C.colid
SET @Retain = @@ERROR IF @Status = 0 SET @Status = @Retain
OPEN Fields
SET @Retain = @@ERROR IF @Status = 0 SET @Status = @Retain
FETCH NEXT FROM Fields INTO @Same
SET @Retain = @@ERROR IF @Status = 0 SET @Status = @Retain
WHILE @@FETCH_STATUS = 0 AND @Status = 0
BEGIN
SET @DBAI = @DBAI + ', ' + @Same
FETCH NEXT FROM Fields INTO @Same
SET @Retain = @@ERROR IF @Status = 0 SET @Status = @Retain
END
CLOSE Fields DEALLOCATE Fields
SET @DBAI = SUBSTRING(@DBAI,3,4000)
DECLARE Fields CURSOR FAST_FORWARD FOR
SELECT Name
FROM #DBAT
ORDER BY Work
OPEN Fields
FETCH NEXT FROM Fields INTO @Same
WHILE @@FETCH_STATUS = 0 AND @Status = 0
BEGIN
IF LEN(@DBAO) < dbao =" @DBAO" status =" 50000" rich =" @Rich" rich =" @Rich" rich =" @Rich" rich =" @Rich" home =" ''" name =" ''" name =" @DBTable" dbultra =" 0" task =" 'IF" name =" '" status =" 0" return =" @@ERROR" status =" 0" status =" @Return" home =" @DBWrite" dbultra =" 0" task =" 'IF" name =" '" status =" 0" return =" @@ERROR" status =" 0" status =" @Return" dbultra =" 0" status =" 0" return =" @@ERROR" status =" 0" status =" @Return" status =" 0" return =" @@ERROR" status =" 0" status =" @Return" status =" 0" return =" @@ERROR" status =" 0" status =" @Return" task =" 'IF" name =" '">
