Comment générer dynamiquement des colonnes dans SQL pour l'analyse des données ?

Créer dynamiquement des colonnes en SQL

La génération de colonnes dynamiques dans SQL permet de créer une structure de données flexible basée sur une entrée dynamique. Considérez le scénario suivant :

• Customers le tableau contient les détails du client (ID et nom)
• CustomerRewards le tableau contient les types de récompenses (TypeID et Description)
• Rewardstableau reliant les clients et les récompenses

L'objectif est de créer dynamiquement des colonnes représentant les types de récompenses et de compter le nombre de récompenses client dans chaque type.

Pour ce faire, vous pouvez utiliser la fonction PIVOT de SQL :

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

Ici, la fonction PIVOT convertit les lignes en colonnes en fonction des description champs, affichant les décomptes pour chaque type de récompense.

Pour un nombre inconnu de colonnes, vous pouvez utiliser du SQL dynamique :

<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 AS c
                LEFT JOIN
                  rewards AS r
                  ON c.id = r.customerid
                LEFT JOIN
                  customerrewards AS cr
                  ON r.typeid = cr.typeid
            ) AS x
            PIVOT 
            (
                COUNT(typeid)
                FOR description IN (' + @cols + ')
            ) AS p ';

EXECUTE (@query);</code>

Les requêtes dynamiques créent des colonnes basées sur les valeurs de la table customerrewards.

Pour inclure la colonne "Total", vous pouvez utiliser l'opérateur ROLLUP :

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

En générant dynamiquement des colonnes, vous pouvez gérer efficacement les structures de données changeantes et présenter les données de manière complète.

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!