SQL相关功能实现.
网上查看行转列.差不多都是对于一个表.今天有时间自己整理下.实现一个相对通用点的.
-- ============================================= -- Author: xin_zhou -- Create date: 2010-03-21 -- Description: 相对通用行转列 -- ============================================= Create PROCEDURE RowToColumn @tablename nvarchar(50), @group_value nvarchar(50), @coulmn_case nvarchar(50), @coulmn_value nvarchar(50), @defaultvalue nvarchar(50)=null, @group_fun nvarchar(10)='max' AS BEGIN declare @coulmntable table (coulmn nvarchar(50)) declare @str_coulmntable nvarchar(120) declare @str_head nvarchar(60) declare @str_tail nvarchar(120) declare @str_case nvarchar(4000) declare @str_default nvarchar(60) set @str_coulmntable = 'select distinct ' + @coulmn_case + ' as coulmn from ' + @tablename insert into @coulmntable exec(@str_coulmntable) set @str_head = 'select '+ @group_value + ' , ' set @str_case = '' if(@defaultvalue is null) set @str_default = ' else null end) as ' else set @str_default = ' else ''' + @defaultvalue + ''' end) as ' select @str_case = @str_case + ' ' + @group_fun + '(case '+ @coulmn_case + ' when ''' + coulmn + ''' then ' + @coulmn_value + @str_default + '''' + coulmn + ''' , ' from @coulmntable set @str_case = left(@str_case,len(@str_case)-1) set @str_tail = ' from ' + @tablename + ' group by ' + @group_value print @str_head+@str_case+@str_tail exec(@str_head+@str_case+@str_tail) END
整个就是差不多如此.参数意思大家对比下面的图就知道了.(关于参数group_value里可以输入多个栏位.用','分开,如'coulmnA,coulmnB,coulmnC')
至于为什么默认的聚合函数为max,是因为行转列应做到一对一.所以聚合函数在这本没有意义.
而别的聚合函数有的只能对int等来取.所以我首看聚合函数能用的范围大就用那个.
2.查询服务器上的所有数据库相关文件(数据文件与日志文件大小.)
主要注意的是这个本意应用游标来完成.但是心中不怎么愿意用游标.结合@@rowcount来模拟游标的实现.暂时达到目标.
declare @ch nvarchar(50) set @ch= '' declare @db_names table([name] nvarchar(50)) insert into @db_names(name) select name from master.dbo.sysdatabases declare @db_file_size table(dbname nvarchar(50),datafilename nvarchar(50),datafilesize nvarchar(50),logfilename nvarchar(50),logfilesize nvarchar(50)) while @@rowcount >0 begin declare @dd table(name nvarchar(50),size nvarchar(50)) select top 1 @ch=[name] from @db_names insert into @dd exec('select name,convert(varchar(50),(size/128)) as size from '+@ch+'.dbo.sysfiles') declare @x_name nvarchar(50) declare @x_size nvarchar(50) select @x_name=[name],@x_size=[size] from @dd where right([name],3) <> 'log' Insert into @db_file_size(dbname,datafilename,datafilesize) values(@ch,@x_name,@x_size) select @x_name=[name],@x_size=[size] from @dd where right([name],3) = 'log' update @db_file_size set logfilename=@x_name,logfilesize=@x_size where dbname = @ch delete @db_names where name=@ch end select distinct * from @db_file_size
先写到这.
有理解错误的地方请大家指正.