1 use EntDataCenter
2 go
3 SET ANSI_NULLS ON
4 GO
5 SET QUOTED_IDENTIFIER ON
6 GO
7 -- =============================================
8 -- Author: <Author,,Name>
9 -- Create date: <Create Date,,>
10 -- Description: <Description,,>
11 -- =============================================
12 --exec dbo.ent_all_DataToExcel
13 create PROCEDURE ent_all_DataToExcel
14
15 AS
16 BEGIN
17 -- SET NOCOUNT ON added to prevent extra result sets from
18 -- interfering with SELECT statements.
19 SET NOCOUNT off;
20 DECLARE @str2name varchar(8000);--二级国代分类名称
21 DECLARE @str2code varchar(8000);--二级国代分类代码
22 DECLARE @str1name varchar(8000);--一级国代分类名称
23 DECLARE @str varchar(8000);
24 DECLARE @str1 varchar(8000);
25 DECLARE @server varchar(100);--服务器
26 DECLARE @uname varchar(100);--用户名
27 DECLARE @pwd varchar(100);--密码
28
29 DECLARE @IsExist bit;
30 select @str2name='';
31 select @str2code='';
32 select @str1name='';
33 select @str='';
34 select @str1='';
35 set @server='192.168.1.7';
36 set @uname='dev_db';
37 set @pwd='dev_db';
38 set @IsExist=0;
39
40 ---判断文件夹是否存在，若不存在则创建文件夹----
41 begin
42 CREATE TABLE #tmp ([File Exists] BIT, [File is a Directory] BIT, [Parent Directory Exists] BIT)
43 INSERT INTO #tmp ([File Exists], [File is a Directory], [Parent Directory Exists])
44 EXEC master.dbo.xp_fileexist 'c:\project';
45 SELECT @IsExist=[File is a Directory] FROM #tmp;
46 if(@IsExist=0)
47 begin
48 ExEc xp_cmdshell 'mkdir c:\project';
49 end
50 drop table #tmp
51 end
52
53 DECLARE contact_cursor CURSOR FOR (SELECT a.国代分类名称 as 二级国代分类名称,a.国代分类代码 as 二级国代分类代码,b.国代分类名称 as 一级国代分类名称
54 FROM [EntDataCenter].[dbo].[doc_class_nation_code] a
55 inner join [EntDataCenter].[dbo].[doc_class_nation_code] b
56 on a.PCode=b.国代分类代码 where a.Level=1)
57 OPEN contact_cursor
58 FETCH NEXT FROM contact_cursor
59 INTO @str2name,@str2code,@str1name
60 WHILE @@FETCH_STATUS = 0
61 BEGIN
62 begin
63 set @str='select a.[企业名称],a.[所在省份],a.[所在城市],' ;
64 set @str=@str+'(case b.year when 2005 then b.[total_assets] else 0 end) as [2005资产],';
65 set @str=@str+'(case b.year when 2006 then b.[total_assets] else 0 end) as [2006资产],';
66 set @str=@str+'(case b.year when 2007 then b.[total_assets] else 0 end) as [2007资产],';
67 set @str=@str+'(case b.year when 2008 then b.[total_assets] else 0 end) as [2008资产],';
68 set @str=@str+'(case b.year when 2009 then b.[total_assets] else 0 end) as [2009资产],';
69 set @str=@str+'(case b.year when 2010 then b.[total_assets] else 0 end) as [2010资产],';
70 set @str=@str+'(case b.year when 2011 then b.[total_assets] else 0 end) as [2011资产],';
71 set @str=@str+'(case b.year when 2012 then b.[total_assets] else 0 end) as [2012资产],';
72 set @str=@str+'(case b.year when 2005 then b.total_current_assets else 0 end) as [2005流动资产],';
73 set @str=@str+'(case b.year when 2006 then b.total_current_assets else 0 end) as [2006流动资产],';
74 set @str=@str+'(case b.year when 2007 then b.total_current_assets else 0 end) as [2007流动资产],';
75 set @str=@str+'(case b.year when 2008 then b.total_current_assets else 0 end) as [2008流动资产],';
76 set @str=@str+'(case b.year when 2009 then b.total_current_assets else 0 end) as [2009流动资产],';
77 set @str=@str+'(case b.year when 2010 then b.total_current_assets else 0 end) as [2010流动资产],';
78 set @str=@str+'(case b.year when 2011 then b.total_current_assets else 0 end) as [2011流动资产],';
79 set @str=@str+'(case b.year when 2012 then b.total_current_assets else 0 end) as [2012流动资产],';
80 set @str=@str+'(case b.year when 2005 then b.total_liabilities else 0 end) as [2005负债],';
81 set @str=@str+'(case b.year when 2006 then b.total_liabilities else 0 end) as [2006负债],';
82 set @str=@str+'(case b.year when 2007 then b.total_liabilities else 0 end) as [2007负债],';
83 set @str=@str+'(case b.year when 2008 then b.total_liabilities else 0 end) as [2008负债],';
84 set @str=@str+'(case b.year when 2009 then b.total_liabilities else 0 end) as [2009负债],';
85 set @str=@str+'(case b.year when 2010 then b.total_liabilities else 0 end) as [2010负债],';
86 set @str=@str+'(case b.year when 2011 then b.total_liabilities else 0 end) as [2011负债],';
87 set @str=@str+'(case b.year when 2012 then b.total_liabilities else 0 end) as [2012负债],';
88 set @str=@str+'(case b.year when 2005 then b.operating_income else 0 end) as [2005收入],';
89 set @str=@str+'(case b.year when 2006 then b.operating_income else 0 end) as [2006收入],';
90 set @str=@str+'(case b.year when 2007 then b.operating_income else 0 end) as [2007收入],';
91 set @str=@str+'(case b.year when 2008 then b.operating_income else 0 end) as [2008收入],';
92 set @str=@str+'(case b.year when 2009 then b.operating_income else 0 end) as [2009收入],';
93 set @str=@str+'(case b.year when 2010 then b.operating_income else 0 end) as [2010收入],';
94 set @str=@str+'(case b.year when 2011 then b.operating_income else 0 end) as [2011收入],';
95 set @str=@str+'(case b.year when 2012 then b.operating_income else 0 end) as [2012收入],';
96 set @str=@str+'(case b.year when 2005 then b.operating_costs else 0 end) as [2005成本],';
97 set @str=@str+'(case b.year when 2006 then b.operating_costs else 0 end) as [2006成本],';
98 set @str=@str+'(case b.year when 2007 then b.operating_costs else 0 end) as [2007成本],';
99 set @str=@str+'(case b.year when 2008 then b.operating_costs else 0 end) as [2008成本],';
100 set @str=@str+'(case b.year when 2009 then b.operating_costs else 0 end) as [2009成本],';
101 set @str=@str+'(case b.year when 2010 then b.operating_costs else 0 end) as [2010成本],';
102 set @str=@str+'(case b.year when 2011 then b.operating_costs else 0 end) as [2011成本],';
103 set @str=@str+'(case b.year when 2012 then b.operating_costs else 0 end) as [2012成本],';
104 set @str=@str+'(case b.year when 2005 then b.total_profit else 0 end) as [2005利润总额],';
105 set @str=@str+'(case b.year when 2006 then b.total_profit else 0 end) as [2006利润总额],';
106 set @str=@str+'(case b.year when 2007 then b.total_profit else 0 end) as [2007利润总额],';
107 set @str=@str+'(case b.year when 2008 then b.total_profit else 0 end) as [2008利润总额],';
108 set @str=@str+'(case b.year when 2009 then b.total_profit else 0 end) as [2009利润总额],';
109 set @str=@str+'(case b.year when 2010 then b.total_profit else 0 end) as [2010利润总额],';
110 set @str=@str+'(case b.year when 2011 then b.total_profit else 0 end) as [2011利润总额],';
111 set @str=@str+'(case b.year when 2012 then b.total_profit else 0 end) as [2012利润总额]';
112 set @str=@str+'from [dbo].[ent_all] a ';
113 set @str=@str+'left join [dbo].[Ent_All_Finance] b on a.ent_id = b.ent_id where a.国代分类代码 like '''+@str2code+'%''';
114
115 --set @str='select * from [EntDataCenter].[dbo].[doc_class_nation_code] where Level=1';
116 set @str1='bcp "'+@str+'" queryout C:\project\'+@str1name+'-'+@str2name+'.xls -c -S'+@server+' -U'+@uname+' -P'+@pwd+'';
117 end
118 --print @str1;
119 EXEC master..xp_cmdshell @str1
120
121
122 FETCH NEXT FROM contact_cursor
123 INTO @str2name,@str2code,@str1name
124 END
125 CLOSE contact_cursor
126 DEALLOCATE contact_cursor
127
128 END
129 GO
浙公网安备 33010602011771号