SQL server 向EXCEL导数据，如何将列名也导出？

0)表T1结构

a	int

b	int

x	char



1)开启Web Assistant Procedures



exec sp_configure 'show advanced options', 1 

RECONFIGURE 

exec sp_configure 'Web Assistant Procedures', 1 

RECONFIGURE 



2)执行如下语句



EXEC sp_makewebtask 

	@outputfile = 'd:\testing.xls', 

	@query = 'Select TOP 10 * from shenliang1985..T1', 

	@colheaders =1, 

	@FixedFont=0,@lastupdated=0,@resultstitle='Querying details'



3)查看生成的EXCEl的



Querying details							



							



Last updated: 2010-03-03 01:02:59.263							



a	b	x					

0	0	0					

2	5	1					

4	10	2					

6	15	3					

8	20	4					

10	25	5					

12	30	6					

14	35	7					

16	40	8					

18	45	9	

0)初始表和数据

SELECT * FROM tb22

--结果

001	A

002	B

003	C



1)创建存储过程p_getColumns_inRow

CREATE PROCEDURE p_getColumns_inRow @tabname VARCHAR(MAX) 

AS

BEGIN

	DECLARE @sql VARCHAR(MAX)

	SELECT @sql= ISNULL(@SQL+',','')+'['+CAST(COLID AS VARCHAR(MAX))+']'

	FROM SYSCOLUMNS 

	WHERE ID = OBJECT_ID(@tabname) GROUP BY COLID

	DECLARE @STRING VARCHAR(MAX)

	SET @STRING='SELECT * FROM (SELECT NAME,COLID FROM SYSCOLUMNS WHERE ID=OBJECT_ID('''+@tabname+'''))A pivot (MAX(NAME) for COLID in('+@sql+'))t'

	EXEC(@STRING)

END



2)启用Ad Hoc Distributed Queries

EXEC sp_configure 'show advanced options', 1 

GO 

RECONFIGURE 

GO 

EXEC sp_configure 'Ad Hoc Distributed Queries', 1 

GO 

RECONFIGURE 

GO



3)取出行列转换后的表tb22的字段到表#testingHow

select   * 

INTO #testingHow

  from   OPENROWSET('SQLOLEDB',   

  'SERVER=PC-20100109AKXW\SHENL;uid=sa;pwd=1qaz2wsx!@12;Database=TestShen','SET   FMTONLY   OFF;SET   NOCOUNT   ON   exec p_getColumns_inRow ''tb22''') 

  as   a





4)将结果合并成含有字段名和字段值的表tb22_Merger中

SELECT * INTO tb22_Merger

FROM #testingHow

UNION ALL

SELECT * FROM tb22



5)整合

SELECT * FROM tb22_Merge



--结果

costNo	costName

001	A

002	B

003	C



6)测试

	6.1)CMD模式



bcp TestShen.dbo.tb22_Merger  out  c:\Temp.xls  -c  -q  -S PC-20100109AKXW\SHENL -U sa  -P 1qaz2wsx!@12



	6.2)xp_cmdshell模式



	--如果没有启用xp_cmdshell,先启动之

	sp_configure 'show advanced options', 1

	GO

	RECONFIGURE

	GO

	sp_configure 'xp_cmdshell', 1

	GO

	RECONFIGURE

	GO



	--xp_cmdshell模式

	EXEC master..xp_cmdshell 'bcp TestShen.dbo.tb22_Merger  out  c:\Temp.xls  -c  -q  -S PC-20100109AKXW\SHENL -U sa  -P 1qaz2wsx!@12'



7)删除表tb22_Merger

DROP TABLE tb22_Merger

老问题，不行就用union all吧，bcp用语句的写法

select '列名1' as a,'列名2' as b

 union all

 select a,b from 表