菜单（列存储转为行存储)

CREATE FUNCTION [dbo].[GetMaxLevel]()
RETURNS INT
AS
BEGIN

 DECLARE @maxLevel INT;
	SET @maxLevel = 1;  
	DECLARE @level INT ; 
    DECLARE @parentidVal NVARCHAR(50);
    DECLARE @natureid NVARCHAR(50);
	DECLARE @naturenm NVARCHAR(50);
	DECLARE @parentid NVARCHAR(50);

    DECLARE  myCursor  CURSOR  FOR SELECT natureid  FROM dbo.CRNATUREM0;
	OPEN myCursor

	FETCH NEXT FROM myCursor INTO @natureid
    WHILE @@FETCH_STATUS = 0
	BEGIN
        SET @level = 1;
		SET @parentidVal = (SELECT parentid FROM dbo.CRNATUREM0 WHERE natureid = @natureid) ;   
		IF EXISTS(SELECT COUNT(*) FROM dbo.CRNATUREM0 WHERE parentid = @natureid)
		BEGIN     
           WHILE(LEN(@parentidVal) > 0)
		   BEGIN
		       SET @level = @level + 1;  
			   SET @parentidVal = (SELECT parentid FROM dbo.CRNATUREM0 WHERE natureid = @parentidVal) ; 
		   END 
		END
        IF( @maxLevel < @level)
		BEGIN
			SET @maxLevel = @level;         
		END 
		FETCH NEXT FROM myCursor INTO @natureid   
	END  

	CLOSE myCursor
    DEALLOCATE myCursor
	RETURN @maxLevel
END
GO

 

CREATE FUNCTION [dbo].[GetLevelByNatureID](
	@natureid nvarchar(20)
)
RETURNS INT
AS
BEGIN
    DECLARE @maxLevel INT;
	SET @maxLevel = 1;  
	DECLARE @level INT ; 
    DECLARE @parentidVal NVARCHAR(50);

    SET @level = 1;
	SET @parentidVal = (SELECT parentid FROM CRNATUREM0 WHERE natureid = @natureid) ;   
	IF EXISTS(SELECT COUNT(*) FROM dbo.CRNATUREM0 WHERE parentid = @natureid)
	BEGIN     
        WHILE(LEN(@parentidVal) > 0)
		BEGIN
		    SET @level = @level + 1;  
			SET @parentidVal = (SELECT parentid FROM dbo.CRNATUREM0 WHERE natureid = @parentidVal) ; 
		END 
	END
    IF( @maxLevel < @level)
	BEGIN
		SET @maxLevel = @level;         
	END 

	RETURN @maxLevel
END
GO

 

 

-- =============================================
-- Author:		JIM
-- Create date: 依据Natureid 获得Nature 明细信息
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[GetNatureValsByID]
	-- Add the parameters for the stored procedure here
    @natureid nvarchar(20),
    @level INT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	
	DECLARE @index INT;
	DECLARE @parentidVal NVARCHAR(20)
	
    -- Insert statements for procedure here
    IF EXISTS ( SELECT  * FROM    sysobjects WHERE   id = OBJECT_ID('tempdb..#natureVal') AND type = 'U' ) 
        DROP TABLE #natureVal;

    CREATE TABLE #natureVal
    (
	    natureKey int ,
		natureVal  nvarchar(100)
    )

	 SET @index = (SELECT COUNT(*)  FROM dbo.CRNATUREM0 WHERE natureid = @natureid)
     SET @level = 0;
     
	 WHILE @index > 0 
	 BEGIN
	     -- 保存Nature数据
	     INSERT #natureVal( natureKey, natureVal )
	     SELECT @level, naturenm FROM dbo.CRNATUREM0 WHERE natureid = @natureid

	     SET @parentidVal = (SELECT parentid  FROM dbo.CRNATUREM0 WHERE natureid = @natureid);
	     SET @natureid = @parentidVal;
	     IF(LEN(@parentidVal) > 0)
	     BEGIN
			 SET @index = (SELECT COUNT(*) FROM dbo.CRNATUREM0 WHERE natureid = @parentidVal);
			 SET @level = @level + 1;
	     END
	     ELSE
	     BEGIN
			SET @index = 0;
	     END
	     
	 END
	 SELECT natureKey, natureVal FROM #natureVal
END

GO

 

 

CREATE PROCEDURE [dbo].[GetNatureDataRow]
AS 
    BEGIN
-- =============================================
-- Script Template
-- =============================================


--DROP TABLE NatureDT ;
       IF object_id('tempdb..#NatureDT') IS NOT NULL
		BEGIN
			DROP TABLE #NatureDT
		END
		
        CREATE TABLE #NatureDT
            (
              natureid NVARCHAR(20) ,
              naturenm NVARCHAR(100)
            )

        DECLARE @maxLevel INT; -- Nature 最大级别数
        DECLARE @level INT;   -- 当前级别变量
        DECLARE @colName NVARCHAR(20);
        DECLARE @sTable NVARCHAR(200);
        SET @level = 1;
        SET @maxLevel = dbo.GetMaxLevel();

        WHILE @level <= @maxLevel 
            BEGIN
                SET @colName = 'nature' + CAST(@level AS CHAR(10));
                EXEC('ALTER TABLE NatureDT ADD '+@colName+'NVARCHAR(100)');
                SET @sTable = 'ALTER TABLE #NatureDT ADD '+@colName+'NVARCHAR(100) ;'
                --EXEC('ALTER TABLE NatureDT ADD '+@colName+'NVARCHAR(100)');
                EXEC(@sTable);
                SET @level = @level + 1;
            END

--- 查询创建的表
        --SELECT  *
        --FROM    NatureDT;


---/////////////////////////////////////////////////

        DECLARE @natureidS NVARCHAR(20);
        DECLARE @natureidSS NVARCHAR(20);
        DECLARE @naturenmS NVARCHAR(100);
        DECLARE @indexS INT;
        DECLARE @levelS INT;
        DECLARE @colnameS NVARCHAR(20);
        DECLARE @parentidVal NVARCHAR(20);
        DECLARE @sUpdate NVARCHAR(200);


        DECLARE nCursor CURSOR
        FOR
            SELECT  natureid
            FROM    dbo.CRNATUREM0
        OPEN nCursor
        FETCH NEXT FROM nCursor INTO @natureidS
        WHILE @@FETCH_STATUS = 0 
            BEGIN
                SET @indexS = ( SELECT  COUNT(*)
                                FROM    dbo.CRNATUREM0
                                WHERE   natureid = @natureidS
                              )
                SET @levelS = dbo.GetLevelByNatureID(@natureidS);
                SET @colnameS = 'nature' + CAST(@levelS AS NVARCHAR(10));
                IF ( @indexS > 0 ) 
                    BEGIN
                        SET @naturenmS = ( SELECT   naturenm
                                           FROM     dbo.CRNATUREM0
                                           WHERE    natureid = @natureidS
                                         );
                        INSERT  #NatureDT
                                ( natureid )
                                SELECT  @natureidS;
                        SET @sUpdate = ' UPDATE #NatureDT SET ' + @colnameS
                            + ' = ''' + @naturenmS + ''' WHERE  natureid = '''
                            + @natureidS + '''';

                        EXEC(@sUpdate);
                    END   
                SET @natureidSS = @natureidS;
     
                WHILE @indexS > 0 
                    BEGIN
	     -- 保存Nature数据
                        SET @parentidVal = ( SELECT parentid
                                             FROM   dbo.CRNATUREM0
                                             WHERE  natureid = @natureidSS
                                           );
                        SET @natureidSS = @parentidVal;
                        IF ( LEN(@parentidVal) > 0 ) 
                            BEGIN
                                SET @indexS = ( SELECT  COUNT(*)
                                                FROM    dbo.CRNATUREM0
                                                WHERE   natureid = @natureidSS
                                              );
                                SET @levelS = @levelS - 1;
			 -- 保存数据
                                SET @colnameS = 'nature'
                                    + CAST(@levelS AS NVARCHAR(10));
                                SET @naturenmS = ( SELECT   naturenm
                                                   FROM     dbo.CRNATUREM0
                                                   WHERE    natureid = @parentidVal
                                                 );
                                SET @sUpdate = ' UPDATE #NatureDT SET '
                                    + @colnameS + '= ''' + @naturenmS
                                    + ''' WHERE natureid = ''' + @natureidS
                                    + '''';
                                EXEC(@sUpdate);
                            END
                        ELSE 
                            BEGIN
                                SET @indexS = 0;
                            END
                    END
                FETCH NEXT FROM nCursor INTO @natureidS
            END
        CLOSE nCursor
        DEALLOCATE nCursor

        SELECT  *
        FROM    #NatureDT

    END
GO