如何有效地將行轉換為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中文網其他相關文章！