--启用Ad Hoc Distributed Queries：

exec sp_configure 'show advanced options',1

reconfigure

exec sp_configure 'Ad Hoc Distributed Queries',1

reconfigure

    --使用完成后，关闭Ad Hoc Distributed Queries：

exec sp_configure 'Ad Hoc Distributed Queries',0

reconfigure

exec sp_configure 'show advanced options',0

reconfigure 

--允许在进程中使用ACE.OLEDB.12

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1

--允许动态参数

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1

--读取excel方式1

select * from OpenRowSet

         ('Microsoft.ACE.OLEDB.12.0',

          'Excel 8.0;HDR=Yes;IMEX=1;Database=c:\data.xls',

           [Sheet1$]

          )

--读取excel方式2

 SELECT * FROM OpenDataSource( 'Microsoft.ACE.OLEDB.12.0','Data Source=c:/data.xls;Extended properties=Excel 8.0')...Sheet1$  

 ----普通表测试

 --if(OBJECT_ID('test@') is not null)

 --drop table test@

 --SELECT *

 --into test@

 --FROM OpenDataSource( 'Microsoft.ACE.OLEDB.12.0','Data Source=c:\data.xls;Extended properties=Excel 8.0')...Sheet1$

 ----select * from test@

 ----打印excel表列名称

 --declare @list nvarchar(max),@sql nvarchar(max)

 --set @list=''

 --set @sql=''

 --select @list=@list+'['+rtrim(b.name)+'],' from sysobjects a,syscolumns b where a.id=b.id and a.name='test@'

 ----print('@list:'+@list)

 --set @sql='select '+left(@list,len(@list)-1)+' from test@'

 --print(@sql)

 --临时表测试

 if(OBJECT_ID('tempdb..#temp') is not null)

 drop table #temp

 SELECT *

 into #temp

 FROM OpenDataSource( 'Microsoft.ACE.OLEDB.12.0','Data Source=c:\data.xls;Extended properties=Excel 8.0')...Sheet1$

 --select * from #temp

 --打印excel表列名称

 declare @list nvarchar(max),@sql nvarchar(max)

 set @list=''

 set @sql=''

 select @list=@list+'['+rtrim(b.name)+'],' from tempdb.sys.columns b where object_id = object_id('tempdb..#temp')

 --print('@list:'+@list)

 set @sql='select '+left(@list,len(@list)-1)+' from #temp'

 print(@sql)

--临时表测试

if(OBJECT_ID('tempdb..#temp') is not null)

drop table #temp

SELECT *

into #temp

FROM OpenDataSource( 'Microsoft.ACE.OLEDB.12.0','Data Source=c:\1.xls;Extended properties=Excel 8.0')...Sheet1$

--select * from #temp

--select distinct AMS ,PAR from #temp

--打印excel表列名称

declare @list nvarchar(max),@sql nvarchar(max),@list1 nvarchar(max),@sql1 nvarchar(max)

,@listC nvarchar(max)

set @list=''

set @sql=''

set @sql1=''

set @list1=''

set @listC=''

select @list=@list+'['+rtrim(b.name)+'],'

--select *

from tempdb.sys.columns b

where object_id = object_id('tempdb..#temp')

----C#赋值

select @listC=@listC+'rowNew["'+rtrim(b.name)+'"]'+'= rowstmp[i]["'+rtrim(b.name)+'"];'

--select *

from tempdb.sys.columns b

where object_id = object_id('tempdb..#temp')

----C#后台页面sql

select @list1=@list1+' '''''''' + convert(nvarchar,ISNULL(t.['+rtrim(b.name)+'],'''')) + '''''''' +'' as '+'['+rtrim(b.name)+'] ,'' +'

--select *

from tempdb.sys.columns b

--cross join (select top  * from #temp) t

where object_id = object_id('tempdb..#temp')

--print('@list:'+@list)

set @sql='select '+left(@list,len(@list)-)+' from #temp'

print(@sql)

--第一行数据作为组建的sql的值

set @sql1='select '+left(@list1,len(@list1)-)+' from (select top 1 * from #temp) t '

print(@sql1)

exec(@sql1)

print(@listC)

select r.phone,[costnum]=COUNT() , [ordernos] = stuff((select ',' + [t].[orderno] from t_red t where r.phone = t.phone for xml path('')) ,  ,  , '')

from t_red r

group by r.phone