把表的竖向列变为横向一字段值(或 竖向变横向)

例:

 表名: tb

内容:
 aa   bb   cc
  12   a    0
  13   b    0
  15   c    0

 

得到结果：
　@sql = '12,13,15'

 

--方法一

declare @sql varchar(1000)
set @sql = ''
select @sql = @sql + t.aa + ',' from (select aa from tb) as t
set @sql='select result = ''' + left(@sql , len(@sql) - 1) + ''''
exec(@sql)


--方法二
declare @output varchar(8000)
select @output = coalesce(@output + ',' , '') + aa from tb
print @output

 

 

--纵向表变成横向表(以sysobjects表为例)
declare @sql varchar(8000)
select @sql = 'select xtype,'
select @sql=@sql + 'Max(case name when '''+ name + ''' then id else null end)''' + name
+ ''',' from  (select * from sysobjects where xtype='u' ) a
select @sql = left(@sql,len(@sql) -1 )
select @sql=@sql + ' from sysobjects where xtype=''u'' group by xtype'
print(@sql)

 

 

 

 

---分组字符串相加 (以sysobjects表为例, 是否包含自动增加的列)

Create   function   f_columns(@a varchar(100))  
  returns   varchar(5000)  
  as  
  begin  
  declare   @return   varchar(5000)  
  select   @return=''  
  select   @return=@return+name+',' from syscolumns where id=@a 
  set @return = left(@return,len(@return)-1)
  return   @return  
  end  
GO 

 

select name,(select name from syscolumns where id=a.id and COLUMNPROPERTY(a.id,name,'IsIdentity')=1) fName,dbo.f_columns(id) allfield from sysobjects a where xtype='u' and name<>'dtproperties'