解决马上结贴！！！！如何用SQL语句把生成的查询结果导出为Excel文件？

[csqiu]

我在SQL Server中作了交叉表的查询,并用DTS作了个任务导出为Excel文件，可是这个查询的字段名称和数目每天都不一样，所以总是不成功，在此请教大虾，如何用SQL语句完成这个查询导出为Excel文件，多谢了！！！！！

[pengdali]

1、建立过程：
CREATE proc out2xls
@服务器名 varchar(255),
@库名 varchar(255),
@表名 varchar(255),
@用户名 varchar(100),
@密码 varchar(100),
@路径及文件名 varchar(255)
as
declare @temp1 nvarchar(4000),@temp2 varchar(8000)

set @temp1='select @value1='''',@value2='''' select @value1=@value1+'',''''''+a.name+''''+char(39)+'' [''+a.name+'']'',@value2=@value2+'',cast(''+''[''+a.name+'']''+ '' as varchar(200))'' from '+@库名+'..syscolumns a,'+@库名+'..sysobjects d where a.id=d.id and d.name='''+@表名+''''+' order by a.colorder'


exec sp_executesql @temp1,N'@value1 nvarchar(4000) output , @value2 varchar(8000) output',@temp1 output,@temp2 output

select @temp1=right(@temp1,len(@temp1)-1),@temp2=right(@temp2,len(@temp2)-1)

exec('select * into '+@库名+'.dbo.中间表 from (select '+ @temp1+' union all SELECT '+@temp2+' FROM '+@库名+'..'+@表名+') tem3')


set @temp2='bcp '+@库名+'.dbo.中间表 out '+@路径及文件名+' -c -S'+@服务器名+' -U'+@用户名+' -P'+@密码

EXEC master..xp_cmdshell @temp2
exec('drop table '+@库名+'.dbo.中间表')
GO

2、把你的交叉查询导到一个临时表比如：
declare @sql varchar(8000)
set @sql = 'select 年份'
select @sql = @sql + ',sum(case 季度 when '''+cast(季度 as varchar(10))+''' then 数据 else 0 end) as ['+cast(季度 as varchar(10))+'季度数据]'
from (select distinct 季度 from 有一表) as a
select @sql = @sql+' into 中间临时表 from 有一表 group by 年份'
exec(@sql)
go

3、调用过程：
exec out2xls 'daliserver','pubs','中间临时表','sa','element','c:\a.xls'

[pengdali]

1、建立过程：
CREATE proc out2xls
@服务器名 varchar(255),
@库名 varchar(255),
@表名 varchar(255),
@用户名 varchar(100),
@密码 varchar(100),
@路径及文件名 varchar(255)
as
declare @temp1 nvarchar(4000),@temp2 varchar(8000)

set @temp1='select @value1='''',@value2='''' select @value1=@value1+'',''''''+a.name+''''+char(39)+'' [''+a.name+'']'',@value2=@value2+'',cast(''+''[''+a.name+'']''+ '' as varchar(200))'' from '+@库名+'..syscolumns a,'+@库名+'..sysobjects d where a.id=d.id and d.name='''+@表名+''''+' order by a.colorder'


exec sp_executesql @temp1,N'@value1 nvarchar(4000) output , @value2 varchar(8000) output',@temp1 output,@temp2 output

select @temp1=right(@temp1,len(@temp1)-1),@temp2=right(@temp2,len(@temp2)-1)

exec('select * into '+@库名+'.dbo.中间表 from (select '+ @temp1+' union all SELECT '+@temp2+' FROM '+@库名+'..'+@表名+') tem3')


set @temp2='bcp '+@库名+'.dbo.中间表 out '+@路径及文件名+' -c -S'+@服务器名+' -U'+@用户名+' -P'+@密码

EXEC master..xp_cmdshell @temp2
exec('drop table '+@库名+'.dbo.中间表')
GO

2、把你的交叉查询导到一个临时表比如：
declare @sql varchar(8000)
set @sql = 'select 年份'
select @sql = @sql + ',sum(case 季度 when '''+cast(季度 as varchar(10))+''' then 数据 else 0 end) as ['+cast(季度 as varchar(10))+'季度数据]'
from (select distinct 季度 from 有一表) as a
select @sql = @sql+' into 中间临时表 from 有一表 group by 年份'
exec(@sql)
go

3、调用过程：
exec out2xls 'daliserver','pubs','中间临时表','sa','element','c:\a.xls'

[nboys]

exec master..xp_cmdshell 'bcp databaseName..tableName out c:\result.xls -c -q -Usa -Ppwd'

[nboys]

create proc bcp_excel
@tableName varchar(100)
as
set nocount on
declare @column varchar(8000)
set @column='select '
select @column = @column + ''''+ name + space(100) +''''+'as ' + name + ',' --如果某个列的长度大于100,那么把space中的数字调大一点
from (select b.name from sysobjects a join syscolumns b on a.id=b.id where a.name=@tableName) tem
set @column = left(@column,len(@column)-1)
--print @column
set @column = @column + ' into ##temp from (select top 1 b.name from sysobjects a join syscolumns b on a.id=b.id where a.name='''+@tableName+''') tem'
exec (@column)
exec('insert into ##temp select * from '+@tableName+'')
exec master..xp_cmdshell 'bcp "select * from ##temp" queryout c:\b.xls -c -q -Usa -Ppwd'
drop table ##temp
set nocount off


执行:exec bcp_excel 'tableName'

[nboys]

or

exec master..xp_cmdshell 'bcp "select * from databaseName..tableName" queryout c:\result.xls -c -q -Usa -Ppwd'

[iamtk]

EXEC master..xp_cmdshell 'bcp "SELECT au_fname, au_lname FROM pubs..authors ORDER BY au_lname" queryout C:\ authors.xls -c -Sservername -Usa -Ppassword'

[愉快的登山者]

drop table #t
create table #t(学生号 varchar(6),地区号 varchar(4),科目 varchar(20),分数 int)
insert into #t
select '00001','0001','(语文)','89'
union all select '00001','0001','(数学)','89'
union all select '00002','0002','(英语)','89'
union all select '00002','0002','(数学)','89'
union all select '00002','0002','(生理)','89'
union all select '00003','0003','(生物)','89'
union all select '00003','0003','(法律)','89'
union all select '00003','0003','(政治)','89'
union all select '00003','0003','(马列)','89'

if exists (select * from sysobjects where name = '_aa' and type = 'u')
drop table _aa

declare @s varchar(3000)
set @s = 'select 学生号, 地区号'
select @s = @s + ',sum(case when 科目 ='''+ 科目 + ''' then 分数 else 0 end) as [' +科目+']' from (select distinct 科目 from #t) A order by 科目
set @s = @s + ' into _aa from #t group by 学生号, 地区号'
exec (@s)

EXEC master..xp_cmdshell 'bcp "select * from db.._aa " queryout c:\aa.xls -c -Sservername -Usa -Ppass'

[FeelingL]

在PB中建立交叉表DW,将DW的结果直接另存为Excel文件