Bagaimana untuk Menjana Lajur secara Dinamik dalam SQL Menggunakan Fungsi PIVOT?

Menjana Lajur Secara Dinamik dalam SQL Menggunakan Fungsi PIVOT

Artikel ini menangani cabaran mencipta lajur dalam SQL secara dinamik untuk mewakili pelbagai jenis ganjaran dalam pangkalan data ganjaran pelanggan. Jenis ganjaran tidak tetap dan boleh berubah dari semasa ke semasa. Kami akan meneroka penyelesaian menggunakan fungsi SQL PIVOT, menunjukkan kedua-dua pendekatan statik dan dinamik, dan termasuk penambahan jumlah lajur untuk hasil yang komprehensif.

Masalahnya:

Kami mempunyai jadual pangkalan data untuk pelanggan, ganjaran mereka dan jenis ganjaran. Jenis ganjaran adalah dinamik; mereka boleh ditambah atau dialih keluar. Matlamatnya ialah untuk menjana set hasil dengan lajur untuk setiap jenis ganjaran, menunjukkan kiraan jenis ganjaran itu untuk setiap pelanggan.

Penyelesaian Statik (Bilangan Tetap Jenis Ganjaran):

Jika jenis ganjaran diketahui terlebih dahulu, pertanyaan PIVOT mudah sudah memadai:

<code class="language-sql">SELECT name, [Bronze], [Silver], [Gold], [Platinum], [AnotherOne]
FROM
(
  SELECT c.name, cr.description, r.typeid
  FROM customers c
  LEFT JOIN rewards r ON c.id = r.customerid
  LEFT JOIN customerrewards cr ON r.typeid = cr.typeid
) x
PIVOT
(
  COUNT(typeid)
  FOR description IN ([Bronze], [Silver], [Gold], [Platinum], [AnotherOne])
) p;</code>

Pertanyaan ini berfungsi dengan baik, tetapi hanya jika jenis ganjaran ("Gangsa," "Perak," dll.) dipratakrifkan.

Penyelesaian Dinamik (Bilangan Jenis Ganjaran Tidak Diketahui):

Untuk bilangan jenis ganjaran yang dinamik, kami memerlukan SQL dinamik:

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

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

SET @query = 'SELECT name,' + @cols + ' FROM 
             (
                SELECT c.name, cr.description, r.typeid
                FROM customers c
                LEFT JOIN rewards r ON c.id = r.customerid
                LEFT JOIN customerrewards cr ON r.typeid = cr.typeid
            ) x
            PIVOT 
            (
                COUNT(typeid)
                FOR description IN (' + @cols + ')
            ) p;';

EXECUTE(@query);</code>

Kod ini membina pertanyaan SQL secara dinamik, membina senarai lajur daripada jadual customerrewards. Ini membolehkan penyesuaian kepada sebarang jenis ganjaran.

Menambah Jumlah Lajur:

Untuk memasukkan jumlah kiraan ganjaran bagi setiap pelanggan, kami boleh menggunakan fungsi ROLLUP (kedua-dua versi statik dan dinamik ditunjukkan di bawah):

Versi Statik dengan Jumlah:

<code class="language-sql">SELECT name, SUM([Bronze]) Bronze, SUM([Silver]) Silver, 
  SUM([Gold]) Gold, SUM([Platinum]) Platinum, SUM([AnotherOne]) AnotherOne
FROM 
(
  SELECT name, [Bronze], [Silver], [Gold], [Platinum], [AnotherOne]
  FROM
  (
    SELECT c.name, cr.description, r.typeid
    FROM customers c
    LEFT JOIN rewards r ON c.id = r.customerid
    LEFT JOIN customerrewards cr ON r.typeid = cr.typeid
  ) x
  PIVOT
  (
    COUNT(typeid)
    FOR description IN ([Bronze], [Silver], [Gold], [Platinum], [AnotherOne])
  ) p
) x
GROUP BY name WITH ROLLUP;</code>

Versi Dinamik dengan Jumlah:

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

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

SELECT @colsRollup 
      = STUFF((SELECT ', SUM(' + QUOTENAME(description) + ') AS ' + QUOTENAME(description)
                    FROM customerrewards
                    GROUP BY description, typeid
                    ORDER BY typeid
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'');


SET @query 
          = 'SELECT name, ' + @colsRollup + '
             FROM
             (
                SELECT name,' + @cols + ' FROM 
                 (
                    SELECT c.name, cr.description, r.typeid
                    FROM customers c
                    LEFT JOIN rewards r ON c.id = r.customerid
                    LEFT JOIN customerrewards cr ON r.typeid = cr.typeid
                ) x
                PIVOT 
                (
                    COUNT(typeid)
                    FOR description IN (' + @cols + ')
                ) p 
              ) x1
              GROUP BY name WITH ROLLUP;';

EXECUTE(@query);</code>

Contoh SQL dinamik ini menyediakan penyelesaian yang mantap dan fleksibel untuk menjana laporan dengan bilangan lajur jenis ganjaran yang berubah-ubah, termasuk jumlah lajur yang berguna. Ingat untuk menyesuaikan nama jadual dan lajur agar sepadan dengan skema pangkalan data khusus anda.

Atas ialah kandungan terperinci Bagaimana untuk Menjana Lajur secara Dinamik dalam SQL Menggunakan Fungsi PIVOT?. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!