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'ASBEGINdeclare @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 ' + @tablenameinsert 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 'elseset @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  @coulmntableset @str_case = left(@str_case,len(@str_case)-1)set @str_tail = ' from ' + @tablename + ' group by ' + @group_valueprint @str_head+@str_case+@str_tailexec(@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.sysdatabasesdeclare @db_file_size table(dbname nvarchar(50),datafilename nvarchar(50),datafilesize nvarchar(50),logfilename nvarchar(50),logfilesize nvarchar(50))while @@rowcount >0begindeclare @dd table(name nvarchar(50),size nvarchar(50))select top 1 @ch=[name]  from @db_namesinsert 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=@chendselect distinct * from @db_file_size

先寫到這.

有理解錯誤的地方請大家指正.