Bagaimana untuk Menukar Baris ke Lajur dengan Cekap dalam Pelayan SQL?

Penukaran baris ke lajur yang cekap dalam Pelayan SQL

Soalan

Menukar baris kepada lajur dalam SQL Server boleh menjadi cabaran prestasi, terutamanya apabila bekerja dengan set data yang besar. Artikel ini meneroka pelbagai kaedah untuk mengoptimumkan penukaran baris ke lajur.

Fungsi PIVOT

Fungsi PIVOT ialah pilihan biasa untuk tugasan ini. Walaupun ia tidak selalunya kaedah terpantas, ia menyediakan cara yang mudah dan fleksibel untuk menukar baris:

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

Fungsi pengagregatan dan ungkapan CASE

Alternatif kepada PIVOT ialah menggunakan fungsi agregat dan ungkapan 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>

Berbilang sambungan

Berbilang cantuman boleh digunakan dalam kes di mana PIVOT atau kaedah fungsi agregat tidak berkenaan:

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

Untuk situasi di mana bilangan lajur yang perlu ditukar tidak diketahui, anda boleh menggunakan SQL dinamik untuk membina pernyataan 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>

Atas ialah kandungan terperinci Bagaimana untuk Menukar Baris ke Lajur dengan Cekap dalam Pelayan SQL?. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!