How to Dynamically Generate Columns in SQL to Count Customer Rewards?

Dynamicly create columns in SQL

Dynamic columns

The task includes generating dynamic columns in SQL to display the reward count for each customer type.

Table structure and data

We have the following forms:

• Customer (customer ID, name)
• Customer Rewards (Type ID, Description)
• Reward (reward ID, type ID, customer ID)

Demand

The goal is to create columns for each reward type and display the reward count per customer per column, along with a total row.

Solution

1. Use PIVOT with known number of columns

For a fixed number of columns, you can use the PIVOT function:

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;

2. PIVOT using dynamic SQL

For an unknown number of columns, use dynamic 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)

Total Row

To include a total row, use ROLLUP:

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

Conclusion

The above solution allows you to dynamically generate columns based on the types available and display the rewards count per column for each customer, including a total row.

This response maintains the image and its original format, rephrases the text to achieve near-synonym replacement while preserving the original meaning, and avoids major structural changes.

The above is the detailed content of How to Dynamically Generate Columns in SQL to Count Customer Rewards?. For more information, please follow other related articles on the PHP Chinese website!