使用ROW_NUMBER()OVER PARTITION BY和Dynamic Columns进行SQL查询

实际问题:我有一个数据库来维护数据库及其所有者。以下查询以所需的格式生成所需的结果,因此没有任何问题。但是,当前返回的列是静态的,我希望它们是动态的。

码:

WITH dow AS (
SELECT dow.Database_ID
    ,d.DatabaseName
    ,d.[Server]
    ,o.Owner_FirstName + ' ' + o.Owner_LastName AS [User]
    ,dow.IsOwner
    ,ROW_NUMBER() OVER (PARTITION BY dow.Database_ID ORDER BY dow.IsOwner DESC) AS r
FROM DatabaseOwner dow
LEFT JOIN [Owner] o ON dow.Owner_ID = o.Owner_ID
LEFT JOIN [Database] d ON dow.Database_ID = d.Database_ID
WHERE d.DatabaseType_ID = 4
),
[rCount] AS (
    SELECT MAX(r) AS [Maxr]
    FROM dow
)

SELECT DatabaseName
    ,[Server]
    ,MAX(CASE r WHEN 1 THEN [User] ELSE '' END) AS [Owner]
    ,MAX(CASE r WHEN 2 THEN [User] ELSE '' END) AS [Alt Owner 1]
    ,MAX(CASE r WHEN 3 THEN [User] ELSE '' END) AS [Alt Owner 2]
    ,MAX(CASE r WHEN 4 THEN [User] ELSE '' END) AS [Alt Owner 3]
FROM dow
GROUP BY DatabaseName
    ,[Server]
ORDER BY DatabaseName
    ,[Server]

结果:

DatabaseName            Server        Owner            Alt Owner 1      Alt Owner 2     Alt Owner 3
db_CatsCradle           cv_Vonnegut   Kurt Vonnegut    Joseph Heller    Douglas Adams   
db_Catch22              cv_Heller     Joseph Heller    Chuck Palahniuk  Joseph Heller   Douglas Adams
db_Hitchhikers          cv_Adams      Douglas Adams    Kurt Vonnegut        
db_InvisibleMonsters    cv_Palahniuk  Chuck Palahniuk  Joseph Heller    

那么,如何为n个替代所有者动态编写此代码?最初,它被设置为限制Alt Owners的数量为3,但是现在我需要扩展它以允许任意数量的Alt Owners。

谢谢。

评论