Comment convertir efficacement les lignes en colonnes dans SQL Server?

Conversion efficace de ligne en colonne dans SQL Server

Question

La conversion de lignes en colonnes dans SQL Server peut constituer un défi de performances, en particulier lorsque vous travaillez avec de grands ensembles de données. Cet article explore différentes méthodes pour optimiser les conversions ligne-colonne.

Fonction PIVOT

La fonction PIVOT est un choix courant pour cette tâche. Bien que ce ne soit pas toujours la méthode la plus rapide, elle offre un moyen simple et flexible de convertir des lignes :

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

Fonctions d'agrégation et expressions CASE

Une alternative à PIVOT consiste à utiliser des fonctions d'agrégation et des expressions 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>

Connexions multiples

Plusieurs jointures peuvent être utilisées dans les cas où les méthodes PIVOT ou fonction d'agrégation ne sont pas applicables :

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

Pour les situations où le nombre de colonnes à convertir est inconnu, vous pouvez utiliser du SQL dynamique pour créer une instruction 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>

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!