En parcourant les noms de colonne avec le SQL dynamique

Je suis juste venu avec une idée pour un morceau de code pour afficher toutes les valeurs distinctes pour chaque colonne, et de compter le nombre d'enregistrements pour chaque. Je veux le code d'une boucle sur toutes les colonnes.

Voici ce que j'ai jusqu'à présent... je suis nouveau sur SQL afin de supporter la noobness 🙂

Dur code:

  select [Sales Manager], count(*)
  from  [BT].[dbo].[test]
  group by [Sales Manager]
  order by 2 desc

Tentative de SQL dynamique:

Declare @sql varchar(max),
@column as varchar(255)

    set @column = '[Sales Manager]'
    set @sql = 'select ' + @column + ',count(*) from [BT].[dbo].[test] group by ' + @column + 'order by 2 desc'

    exec (@sql)

Ces deux fonctionnent très bien. Comment puis-je faire en boucle sur toutes les colonnes? Je n'ai pas l'esprit si j'ai coder en dur les noms de colonne et il travaille son chemin à travers le substrat dans chaque un pour @colonne.

Cela fait-il sens?

Merci à tous!

OriginalL'auteur Lucas | 2013-12-09

  1. 7

    Vous pouvez utiliser le SQL dynamique et d'obtenir tous les noms de colonne d'une table. Puis construire le script:

    Declare @sql varchar(max) = ''
declare @tablename as varchar(255) = 'test'

select @sql = @sql + 'select [' + c.name + '],count(*) as ''' + c.name +  ''' from [' + t.name + '] group by [' + c.name + '] order by 2 desc; ' 
from sys.columns c
inner join sys.tables t on c.object_id = t.object_id
where t.name = @tablename

EXEC (@sql)

    Changement @tablename le nom de votre table (sans la base de données ou le nom du schéma).

    J'ai essayé ce code... Il semblait fonctionner, mais en suis-je censé ajouter quelque chose? Je ne vois pas une boucle? Cheers,
    Vous n'avez pas besoin d'une boucle, toutes les requêtes pour toutes les colonnes sont en @sql variable.
    Hey Szymon. Bon, je crois que j'ai trouvé... haha. J'obtiens 'commande s'est terminée avec succès", mais aucun résultat n'est affiché... Quelle partie de votre code dois-je changer à mes propres valeurs?
    Modification @tablename est le nom de votre table (sans la base de données ou le nom du schéma).
    Ah droite. Pour copier votre code exactement... Rien ne se passe malheureusement. 🙁 Les résultats doivent apparaître dans la fenêtre de résultats de droit?

    OriginalL'auteur Szymon

  2. 8

    C'est un peu un XY réponse, mais si vous n'avez pas l'esprit de coder en dur les noms de colonnes, je vous suggère de le faire, et éviter de SQL dynamique - et la boucle entièrement. SQL dynamique est généralement considéré comme le dernier recours, vous ouvre à des problèmes de sécurité (attaques par injection SQL) si vous ne faites pas attention, et peut souvent être plus lent si les requêtes et les plans d'exécution ne peut pas être mis en cache.

    Si vous avez une tonne de noms de colonnes, vous pouvez écrire un petit morceau de code ou de fusion et publipostage dans Word pour effectuer la substitution pour vous.

    Cependant, aussi loin que la façon d'obtenir les noms de colonne, en supposant que c'est SQL Server, vous pouvez utiliser la requête suivante:

    SELECT c.name
FROM sys.columns c
WHERE c.object_id = OBJECT_ID('dbo.test')

    Par conséquent, vous pouvez construire votre dynamique SQL de cette requête:

    SELECT 'select ' 
    + QUOTENAME(c.name) 
    + ',count(*) from [BT].[dbo].[test] group by ' 
    + QUOTENAME(c.name)  
    + 'order by 2 desc'
FROM sys.columns c
WHERE c.object_id = OBJECT_ID('dbo.test')

    et boucle à l'aide d'un curseur.

    Ou de compiler le tout ensemble dans un lot et d'exécution. Ici, nous utilisons la FOR XML PATH('') astuce:

    DECLARE @sql VARCHAR(MAX) = (
    SELECT ' select ' --note the extra space at the beginning
        + QUOTENAME(c.name) 
        + ',count(*) from [BT].[dbo].[test] group by ' 
        + QUOTENAME(c.name)  
        + 'order by 2 desc'
    FROM sys.columns c
    WHERE c.object_id = OBJECT_ID('dbo.test')
    FOR XML PATH('')
)

EXEC(@sql)

    Remarque, je suis en utilisant le haut-QUOTENAME function pour échapper à des noms de colonnes qui ont besoin de s'échapper.

    OriginalL'auteur lc.