SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

ALTER  PROCEDURE CROSSTAB
AS
BEGIN
    DECLARE @AREANAME VARCHAR(50)
    DECLARE @F_CLASS2 VARCHAR(50)
    DECLARE @AR_MONEY DECIMAL(18,2)
    DECLARE @CREATESQL VARCHAR(200)
    DECLARE @INSERTSQL VARCHAR(200)

    CREATE TABLE #TMP(项目 VARCHAR(50))
    DECLARE CREATECURSOR CURSOR FOR SELECT DISTINCT AREANAME FROM DINING_AREA
    DECLARE INSERTCURSOR CURSOR FOR SELECT ISNULL(SUM(AR_MONEY),0) AS AR_MONEY,F_CLASS2,AREANAME FROM (SELECT ISNULL(SUM(AR_MONEY),0) AS AR_MONEY,F_CLASS2,(SELECT AREANAME FROM DINING_AREA WHERE ID=(SELECT AREAID FROM DISHTABLE WHERE ID=(SELECT TABLEID FROM TABLEORDERS WHERE ID=(SELECT TABLEORDERSID FROM ORDERDISH WHERE ID=O.ID)))) AS AREANAME FROM ORDERDISH O GROUP BY F_CLASS2,ID) A GROUP BY F_CLASS2,AREANAME ORDER BY F_CLASS2,AREANAME
    OPEN CREATECURSOR
    FETCH NEXT FROM CREATECURSOR INTO @AREANAME
    WHILE @@FETCH_STATUS=0
    BEGIN
        IF NOT @AREANAME IS NULL
        BEGIN
            SET @CREATESQL='ALTER TABLE #TMP ADD '+@AREANAME+' DECIMAL(18,2)'
            EXEC(@CREATESQL)
            
        END
        FETCH NEXT FROM CREATECURSOR INTO @AREANAME
    END

    OPEN INSERTCURSOR    
    FETCH NEXT FROM INSERTCURSOR INTO @AR_MONEY,@F_CLASS2,@AREANAME
            WHILE @@FETCH_STATUS=0
            BEGIN
                SET @INSERTSQL='INSERT INTO #TMP (项目,'+ @AREANAME +') VALUES('''+ @F_CLASS2 +''','+ @AR_MONEY +')'
                EXEC(@INSERTSQL)
                FETCH NEXT FROM INSERTCURSOR INTO @AR_MONEY,@F_CLASS2,@AREANAME
            END
    CLOSE CREATECURSOR
    DEALLOCATE CREATECURSOR
    CLOSE INSERTCURSOR
    DEALLOCATE INSERTCURSOR

END




GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO