How to Efficiently Convert Rows to Columns in SQL Server?

SQL Server's medium -efficient rotation conversion

Question

It may be a performance challenge in SQL Server, especially when dealing with large datasets. This article discusses various methods for optimizing row conversion conversion.

Pivot function

Pivot function is a common choice for this task. Although it is not always the fastest way, it provides a direct and flexible way of conversion:

The polymer function and case expression

<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>

Pivot's replacement method is to use the polymer function and case expression:

Multiple connections

In the case of PIVOT or polymerization function, multiple connections can be used:

<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>

dynamic pivot

For the unknown case that needs to be converted, you can use a dynamic SQL to build a PIVOT statement:

<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>

The above is the detailed content of How to Efficiently Convert Rows to Columns in SQL Server?. For more information, please follow other related articles on the PHP Chinese website!