这是我迄今为止所做的...我是新来的,所以忍受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 descAttempt 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的列名
发布评论