循环使用动态SQL的列名 [英] Looping through column names with dynamic SQL

问题描述

我刚刚想出了一个代码，以显示每个列的所有不同的值，并计算每个的记录数。我想让代码循环遍历所有列。

这是我迄今为止所做的...我是新来的，所以忍受noobness：）

硬码：

 选择[销售经理]，count（*）$ [dbo]。[test] 
 group by [Sales Manager] 
 order by 2 desc 
  

尝试使用动态SQL：

 声明@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）
  pre> 
 
 
这两个都可以正常工作。如何使其循环遍历所有列？我不介意，如果我必须硬编码列名称，它的工作方式是通过在每一个@column中的方式。
 
 
 
这是否有意义？ p> 
 
 
感谢所有！
解决方案
您可以使用动态SQL并获取所有一个表的列名。然后建立脚本：
 声明@sql varchar（max）=''
将@tablename声明为varchar 255）='test'
 
 select @sql = @sql +'select ['+ c.name +']，count（*）as'''+ c.name +'''from ['+ t.name +'] group by ['+ c.name +'] order by 2 desc; '
从sys.columns c 
内部连接sys.tables t在c.object_id = t.object_id 
其中t.name = @tablename 
 
 EXEC（@ sql）
  
将 @tablename 更改为名称（没有数据库或模式名称）。
 
I just came up with an idea for a piece of code to show all the distinct values for each column, and count how many records for each. I want the code to loop through all columns.

Here's what I have so far... I'm new to SQL so bear with the noobness :)

Hard code:
  select [Sales Manager], count(*)
  from  [BT].[dbo].[test]
  group by [Sales Manager]
  order by 2 desc
Attempt at dynamic SQL:
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)
Both of these work fine. How can I make it loop through all columns? I don't mind if I have to hard code the column names and it works its way through subbing in each one for @column.

Does this make sense?

Thanks all!
解决方案
You can use dynamic SQL and get all the column names for a table. Then build up the 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)
Change @tablename to the name of your table (without the database or schema name).

                        
这篇关于循环使用动态SQL的列名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！