通过存储过程创建带有动态列的视图 [英] Creating View with dynamic columns by stored procedure

问题描述

我一直在执行一个存储过程来生成一个带有动态列的视图.此视图应表示以下 PIVOT SQL 语句，如下所示:

I'm stuck in implementing a stored procedure for generating a view with dynamically columns. This view should represent following PIVOT SQL-Statement as shown here:

SQL Fiddle 演示

为什么我使用带有动态 SQL 的存储过程?要求是，要实现一个数据库对象，它的行为就像显示的语句在 MSSQL 2005 上工作直到现在以及从 9i 开始的 Oracle 上(不幸的是......).现在，由于缺少关于如何以更好的方式实现的更深入的知识，我决定使用存储过程.

Why i am using a stored procedure with dynamic SQL? The requirements are, to implement a database object, which behaves like shown statement working on MSSQL 2005 until now and also on Oracle starting with 9i (unfortunatelly...). Now, because of missing deeper knowledge on how to implement in a better way, i decided to use a stored procedure.

但现在我卡住了，非常感谢任何建议.也欢迎任何关于如何以更好的方式实施但具有与显示的 SQL Fiddle Demo 相同的结果的建议.

But now im stuck and any suggestions are highly appreciated. Also any suggestion on how to implement in a better way but with the same result like shown SQL Fiddle Demo are welcome.

我的 SP 目前看起来像这样:

My SP currently looks like that:

create procedure GeneratePivotLeistungsbewertungen
as
SET NOCOUNT ON;

DECLARE cActivityNames CURSOR FOR  
    SELECT distinct ActivityName
    FROM Leistungsbewertungen

DECLARE @name VARCHAR(32)
DECLARE @dyn_col_list NVARCHAR(MAX)

OPEN cActivityNames  
FETCH NEXT FROM cActivityNames INTO @name  

WHILE @@FETCH_STATUS = 0  
BEGIN  
       SET @dyn_col_list = @dyn_col_list + N'(select lb.Bewertungswert from Leistungsbewertungen lb where lb.ActivityName = '+ @name +' and lb.LeistungsId = o.LeistungsId)' + @name + ','

       FETCH NEXT FROM cActivityNames INTO @name  
END   
deallocate cActivityNames

select LEFT(@dyn_col_list, LEN(@dyn_col_list)-1)

DECLARE @execVar NVARCHAR(MAX) = N' VIEW dbo.PivotLeistungsbewertungen 
    AS
        SELECT max(o.LeistungsId) LeistungsId, max(o.Gnr) GOP,' + @dyn_col_list + 
         'FROM leistungen o group by o.LeistungsId'

SET @execVar = CASE WHEN EXISTS
(SELECT 1 FROM sys.views WHERE [object_id] = OBJECT_ID('dbo.PivotLeistungsbewertungen'))
THEN N'ALTER' ELSE N'CREATE' + @execVar END;

  BEGIN TRY
    EXEC sp_executesql @execVar;
  END TRY
  BEGIN CATCH
    PRINT ERROR_MESSAGE();
  END CATCH  
go

如果你这样称呼它为什么不创建视图的任何想法:

Any ideas why it doesn't create the view if you call it like that:

USE [Q20133_0_NO]
GO

DECLARE @return_value int

EXEC    @return_value = [dbo].[GeneratePivotLeistungsbewertungen]

SELECT  'Return Value' = @return_value

GO

针对解决方案 (2005/2008R2) 进行编辑

更新了工作存储过程(在 SQLServer 2008 R2 下测试

CREATE PROCEDURE GeneratePivotLeistungsbewertungen
AS
    SET NOCOUNT ON;

    DECLARE @name VARCHAR(32)
    DECLARE @dyn_col_list NVARCHAR(MAX) = ''

    SELECT @dyn_col_list = isnull(@dyn_col_list + ',', '') + N'(
              select lb.Bewertungswert
              from Leistungsbewertungen lb
              where
                  lb.ActivityName = ''' + ActivityName + ''' and
                  lb.LeistungsId = o.LeistungsId
        )' + ActivityName
    FROM Leistungsbewertungen
    GROUP BY ActivityName

    DECLARE @execVar NVARCHAR(MAX) = N' VIEW dbo.PivotLeistungsbewertungen 
        AS
            SELECT max(o.LeistungsId) LeistungsId, max(o.Gnr) GOP' + @dyn_col_list + ' FROM leistungen o group by o.LeistungsId'

    SET @execVar = CASE 
            WHEN EXISTS (
                    SELECT 1
                    FROM sys.VIEWS
                    WHERE [object_id] = OBJECT_ID('dbo.PivotLeistungsbewertungen')
                    )
                THEN N'ALTER' + @execVar
            ELSE N'CREATE' + @execVar
            END;

    BEGIN TRY
        EXEC sp_executesql @execVar;
    END TRY

    BEGIN CATCH
        PRINT ERROR_MESSAGE();
    END CATCH

对于 SQLServer 2005，它必须遵循实现(也适用于 2008 R2)

DECLARE @execVar NVARCHAR(200) 
SET @execVar = CASE 
            WHEN EXISTS (
                    SELECT 1
                    FROM sys.procedures
                    WHERE [object_id] = OBJECT_ID('dbo.GeneratePivotLeistungsbewertungen')
                    )
                THEN N'DROP PROCEDURE GeneratePivotLeistungsbewertungen;'
            END;
EXEC(@execVar)
go

CREATE PROCEDURE GeneratePivotLeistungsbewertungen
AS
    SET NOCOUNT ON;

    DECLARE @name VARCHAR(32)
    DECLARE @dyn_col_list NVARCHAR(MAX) 

    SET @dyn_col_list = ''

    SELECT @dyn_col_list = isnull(@dyn_col_list + ',', '') + N'(
              select lb.Bewertungswert
              from Leistungsbewertungen lb
              where
                  lb.ActivityName = ''' + ActivityName + ''' and
                  lb.LeistungsId = o.LeistungsId
        ) AS ' + ActivityName
    FROM Leistungsbewertungen
    GROUP BY ActivityName
    ORDER BY MAX(Bewertungsschritt)

    DECLARE @execVar NVARCHAR(MAX)
    SET @execVar = N' VIEW dbo.PivotLeistungsbewertungenView
        AS
            SELECT max(o.LeistungsId) LeistungsId, max(o.Gnr) GOP' + @dyn_col_list + ' FROM leistungen o group by o.LeistungsId'

    SET @execVar = CASE 
            WHEN EXISTS (
                    SELECT 1
                    FROM sys.VIEWS
                    WHERE [object_id] = OBJECT_ID('dbo.PivotLeistungsbewertungenView')
                    )
                THEN N'ALTER' + @execVar
            ELSE N'CREATE' + @execVar
            END;

    BEGIN TRY
        EXEC sp_executesql @execVar;
    END TRY

    BEGIN CATCH
        PRINT ERROR_MESSAGE();
    END CATCH
GO

推荐答案

我几乎可以肯定你只需要初始化你的 @dyn_col_list 就可以了:

I almost certain that you just have to initialize your @dyn_col_list and you'll be ok:

set @dyn_col_list = ''

在打开光标之前.无论如何，如果你有这样的问题，最好的快速检查方法就是在执行之前print @execVar 以确保你正在执行什么.

before opening cursor. Anyway, if you have a problems like that, best way to fast check it is just to print @execVar before execute to be sure what exactly you're executing.

另外，我通常使用这种语法代替游标:

Also, I usually use this syntax instead of cursor:

select
    @dyn_col_list = isnull(@dyn_col_list + ',', '') +
    N'(
          select lb.Bewertungswert
          from Leistungsbewertungen lb
          where
              lb.ActivityName = '+ ActivityName +' and
              lb.LeistungsId = o.LeistungsId
    )' + ActivityName
from Leistungsbewertungen
group by ActivityName

它更短，自动消除最后一个逗号，你不需要初始化 @dyn_col_list(好吧，事实上你不必初始化它或用 null 初始化它，如果它有可能不为空)

It's shorter, automatically eliminates last comma and you don't need to initialize @dyn_col_list (well, in fact you have to NOT initialize it or initialize it with null if there some possibility that it could be not null)

这篇关于通过存储过程创建带有动态列的视图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！