sql-server - 基于列值的透视，在多列中输出

Question

我有一张桌子：

主机名	应用
SRV000001	应用程序1
SRV000001	应用程序2
SRV000002	应用程序3
SRV000003	应用程序1
SRV000003	应用程序4
SRV000003	应用程序5

我需要围绕主机名列进行旋转/分组，以便结果类似于。由于要获取的列数不固定，我发现很难在此上使用数据透视函数。

主机名	Col1	Col2	Col3
SRV000001	应用程序1	应用程序2
SRV000002	应用程序3
SRV000003	应用程序1	应用程序4	应用程序5

标签： sql-servertsqlpivot

Answer 1

可以使用ROW_NUMBER()窗口函数对列进行编号以确定列标题，例如Col1, Col2..，然后可以通过使用此查询应用条件聚合：

SELECT [HostName], 
       MAX(CASE WHEN [rn]=1 THEN [Application] END) AS [Col1],
       MAX(CASE WHEN [rn]=2 THEN [Application] END) AS [Col2],
       MAX(CASE WHEN [rn]=3 THEN [Application] END) AS [Col3]
  FROM( 
       SELECT *,
              ROW_NUMBER() OVER 
              (PARTITION BY [HostName] ORDER BY [Application]) AS [rn]
         FROM [t]
      ) AS t
 GROUP BY [HostName]

查询可以通过以下代码块转换为动态查询：

DECLARE @cols  AS NVARCHAR(MAX),  @query AS NVARCHAR(MAX)

SET @cols = ( SELECT STRING_AGG('MAX(CASE WHEN [rn]='+
                                 CAST([rn] AS VARCHAR)+
                               ' THEN [Application] END) AS [Col'+
                                 CAST([rn] AS VARCHAR)+
                               ']',',') 
                FROM (SELECT DISTINCT ROW_NUMBER() OVER 
                            (PARTITION BY [HostName] ORDER BY [HostName]) AS [rn]
                        FROM [t] ) t );

SET  @query = 
     N'SELECT [HostName],' + @cols + 
     N'  FROM( 
              SELECT *,
                     ROW_NUMBER() OVER 
                     (PARTITION BY [HostName] ORDER BY [Application]) AS [rn]
                FROM [t]
             ) AS t
        GROUP BY [HostName]';

EXEC sp_executesql @query;

Demo