Bagaimana untuk Menjana dan Mengisi Lajur Secara Dinamik dengan Nilai Kiraan dalam SQL menggunakan PIVOT?

Penjanaan lajur dinamik SQL dan pengisian kiraan

Buat lajur dalam SQL secara dinamik dan isikannya dengan nilai kiraan, biasanya menggunakan fungsi PIVOT.

Penjanaan lajur statik:

Jika bilangan lajur diketahui, anda boleh menggunakan nilai kod keras dalam pernyataan PIVOT:

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

Penjanaan lajur dinamik:

Untuk bilangan lajur yang tidak diketahui, SQL dinamik boleh digunakan:

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

mengandungi jumlah lajur:

Untuk menambah jumlah lajur, anda boleh menggunakan fungsi ROLLUP:

Versi statik:

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

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

Atas ialah kandungan terperinci Bagaimana untuk Menjana dan Mengisi Lajur Secara Dinamik dengan Nilai Kiraan dalam SQL menggunakan PIVOT?. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!