如何有效地将行转换为SQL Server中的列？

SQL Server中高效的行转列转换

问题

在SQL Server中将行转换为列可能是一个性能挑战，尤其是在处理大型数据集时。本文探讨了优化行转列转换的各种方法。

PIVOT函数

PIVOT函数是此任务的常用选择。虽然它并不总是最快的方法，但它提供了一种直接且灵活的行转换方式：

<code class="language-sql">SELECT FirstName, Amount, PostalCode, LastName, AccountNumber
FROM (
  SELECT value, columnName
  FROM yourTable
) d
PIVOT (
  MAX(value)
  FOR columnName IN (FirstName, Amount, PostalCode, LastName, AccountNumber)
) piv;</code>

聚合函数和CASE表达式

PIVOT的替代方法是使用聚合函数和CASE表达式：

<code class="language-sql">SELECT
  MAX(CASE WHEN columnName = 'FirstName' THEN value END) Firstname,
  MAX(CASE WHEN columnName = 'Amount' THEN value END) Amount,
  MAX(CASE WHEN columnName = 'PostalCode' THEN value END) PostalCode,
  MAX(CASE WHEN columnName = 'LastName' THEN value END) LastName,
  MAX(CASE WHEN columnName = 'AccountNumber' THEN value END) AccountNumber
FROM yourTable;</code>

多重连接

在PIVOT或聚合函数方法不适用的情况下，可以使用多重连接：

<code class="language-sql">SELECT fn.value AS FirstName,
  a.value AS Amount,
  pc.value AS PostalCode,
  ln.value AS LastName,
  an.value AS AccountNumber
FROM yourTable fn
LEFT JOIN yourTable a
  ON fn.someCol = a.someCol AND a.columnName = 'Amount'
LEFT JOIN yourTable pc
  ON fn.someCol = pc.someCol AND pc.columnName = 'PostalCode'
LEFT JOIN yourTable ln
  ON fn.someCol = ln.someCol AND ln.columnName = 'LastName'
LEFT JOIN yourTable an
  ON fn.someCol = an.someCol AND an.columnName = 'AccountNumber'
WHERE fn.columnName = 'Firstname';</code>

动态PIVOT

对于需要转换的列数未知的情况，可以使用动态SQL来构建PIVOT语句：

<code class="language-sql">DECLARE @cols AS NVARCHAR(MAX),
    @query AS NVARCHAR(MAX);

SELECT @cols = STUFF((SELECT ',' + QUOTENAME(ColumnName)
                FROM yourTable
                GROUP BY ColumnName, id
                ORDER BY id
        FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)')
    ,1,1,'');

SET @query = N'SELECT ' + @cols + N' FROM
         (
            SELECT value, ColumnName
            FROM yourTable
        ) x
        PIVOT
        (
            MAX(value)
            FOR ColumnName IN (' + @cols + N')
        ) p ';

EXEC sp_executesql @query;</code>

以上是如何有效地将行转换为SQL Server中的列？的详细内容。更多信息请关注PHP中文网其他相关文章！