How to Dynamically Generate and Populate Columns with Count Values in SQL using PIVOT?

SQL dynamic column generation and count filling

Dynamicly create columns in SQL and populate them with count values, usually using the PIVOT function.

Static column generation:

If the number of columns is known, you can use hardcoded values ​​in the PIVOT statement:

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

Dynamic column generation:

For an unknown number of columns, dynamic SQL can be used:

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

contains total column:

To add a total column, you can use the ROLLUP function:

Static version:

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

Dynamic version:

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

The above is the detailed content of How to Dynamically Generate and Populate Columns with Count Values in SQL using PIVOT?. For more information, please follow other related articles on the PHP Chinese website!