行合并问题：SQL语句中一条常见的行合并问题
使用 SP_executesql 从exec('SQL语句') 中得到一个返回值
另：行转列,列分行,行合并列,etc. （整理贴）
自己写的代码：自己加工到可以将连续的号码用“-”表示，如：10-19
Code
1 -- =============================================
2 -- Create AgrNumber Number Stat procedure
3 -- 查询某所属期的协议书使用情况（如查询所属期为200903的数据，请设置参数 @sdate = '200903'）
4 -- 分公司名称不能空
5 -- =============================================
6
7 -- Drop stored procedure if it already exists
8 IF EXISTS (
9 SELECT *
10 FROM INFORMATION_SCHEMA.ROUTINES
11 WHERE SPECIFIC_SCHEMA = N'dbo'
12 AND SPECIFIC_NAME = N'AgreementNumberStat'
13 )
14 DROP PROCEDURE dbo.AgreementNumberStat
15 GO
16
17 CREATE PROCEDURE dbo.AgreementNumberStat
18 @sdate varchar(6) = convert(varchar(6),GetDate(),112)--查询所属期
19 @company varchar(8) = '%%'--查询分公司
20
21 AS
22 declare @misdata varchar(10)--分公司对应的数据库名称
23 declare @strsql nvarchar(2000)
24
25 select @misdata = DataBaseName from FilialeInfor where ForShort like @company--获取分公司对应的数据库
26
27 DECLARE @Result TABLE
28 (
29 ID int IDENTITY(1,1),
30 NumberPrefix smallint,
31 UsedAgrNumbers varchar(max),--已经使用的协议编号
32 UsedAmount int,--已使用数
33 UnUsedAgrNumbers varchar(max),--未使用的协议编号
34 UnUsedAmount int,--未使用数
35 DisuseAgrNumbers varchar(max),--作废的协议编号
36 DisuseAmount int--作废数
37 )
38
39 if @misdata is null
40 SELECT NumberPrefix,UsedAgrNumbers,UsedAmount,UnUsedAgrNumbers,UnUsedAmount,DisuseAgrNumbers,DisuseAmount
41 FROM @Result
42 else
43 begin
44 set @strsql = ' select NumberPrefix'
45 + ' ,sum(case when convert(varchar(6),UsedDate,112) = ' + @sdate + ' then 1 else 0 end) as Used'
46 + ' ,sum(case when (UsedDate is null or convert(varchar(8),UsedDate,112) > ' + @sdate + '31) then 1 else 0 end) as UnUsed'
47 + ' ,sum(case when convert(varchar(6),ChangeDate,112) = ' + @sdate
48 + ' and (agr.Status = 4) then 1 else 0 end) as Disuse'
49 + ' from AgreementNumber as an left join '+ @misdata +'.dbo.Agreement as agr'
50 + ' on an.AgrNumber = agr.AgrNumber and an.NumberPrefix = agr.NumberKind'
51 + ' where OutputCompany like ''' + @company + ''''
52 + ' and OutPutCompany is not NULL'
53 + ' group by NumberPrefix'
54 INSERT INTO @Result (NumberPrefix,UsedAmount,UnUsedAmount,DisuseAmount)
55 exec (@strsql)
56
57 DECLARE @i int
58 ,@j int
59 ,@numberprefix smallint
60 ,@agrnums nvarchar(max)
61
62 SELECT @i=1,@j=ISNULL(COUNT(1),0) FROM @Result
63 WHILE @i<=@j
64 BEGIN
65 select @numberprefix = NumberPrefix from @Result Where ID = @i
66
67 --获取已经使用的编号
68 set @strsql = 'Declare @tmpnum1 bigint,@tmpnum2 bigint'
69 + ' SET @agrnumbers = '''' SET @tmpnum1 = -2 set @tmpnum2 = -2'
70 + ' select @agrnumbers = @agrnumbers + case '
71 + ' when @tmpnum2 + 1 = AgrNumber then '''' '
72 + ' when @agrnumbers = '''' then convert(varchar(20),AgrNumber) '
73 + ' when @tmpnum1 + 1 <> @tmpnum2 then '','' + convert(varchar(20),AgrNumber) '
74 + ' else ''-'' + convert(varchar(20),@tmpnum2) + '','' + convert(varchar(20),AgrNumber) '
75 + ' end, @tmpnum1 = @tmpnum2,@tmpnum2 = AgrNumber'
76 + ' from (select an.AgrNumber from AgreementNumber as an left join '+ @misdata +'.dbo.Agreement as agr'
77 + ' on an.AgrNumber = agr.AgrNumber and an.NumberPrefix = agr.NumberKind'
78 + ' where OutputCompany like ''' + @company + ''''
79 + ' and OutPutCompany is not NULL'
80 + ' and convert(varchar(6),UsedDate,112) = ' + @sdate
81 + ' and NumberPrefix = ' + convert(varchar(8),@numberprefix)
82 + ' union select 9223372036854775807) as tmpt'
83 execute SP_executesql @strsql,N'@agrnumbers varchar(max) output',@agrnums output
84 IF(len(@agrnums) > 20)
85 UPDATE @Result SET UsedAgrNumbers = left(@agrnums , len(@agrnums) - 20) WHERE ID = @i
86
87 --获取未使用的编号
88 set @strsql = 'Declare @tmpnum1 bigint,@tmpnum2 bigint'
89 + ' SET @agrnumbers = '''' SET @tmpnum1 = -2 set @tmpnum2 = -2'
90 + ' select @agrnumbers = @agrnumbers + case '
91 + ' when @tmpnum2 + 1 = AgrNumber then '''' '
92 + ' when @agrnumbers = '''' then convert(varchar(20),AgrNumber) '
93 + ' when @tmpnum1 + 1 <> @tmpnum2 then '','' + convert(varchar(20),AgrNumber) '
94 + ' else ''-'' + convert(varchar(20),@tmpnum2) + '','' + convert(varchar(20),AgrNumber) '
95 + ' end, @tmpnum1 = @tmpnum2,@tmpnum2 = AgrNumber'
96 + ' from (select an.AgrNumber from AgreementNumber as an left join '+ @misdata +'.dbo.Agreement as agr'
97 + ' on an.AgrNumber = agr.AgrNumber and an.NumberPrefix = agr.NumberKind'
98 + ' where OutputCompany like ''' + @company + ''''
99 + ' and OutPutCompany is not NULL'
100 + ' and (UsedDate is null or convert(varchar(8),UsedDate,112) > ' + @sdate + '31)'
101 + ' and NumberPrefix = ' + convert(varchar(8),@numberprefix)
102 + ' union select 9223372036854775807) as tmpt'
103 execute SP_executesql @strsql,N'@agrnumbers varchar(max) output',@agrnums output
104 IF(len(@agrnums) > 20)
105 UPDATE @Result SET UnUsedAgrNumbers = left(@agrnums , len(@agrnums) - 20) WHERE ID = @i
106
107 --获取作废的编号
108 set @strsql = 'Declare @tmpnum1 bigint,@tmpnum2 bigint'
109 + ' SET @agrnumbers = '''' SET @tmpnum1 = -2 set @tmpnum2 = -2'
110 + ' select @agrnumbers = @agrnumbers + case '
111 + ' when @tmpnum2 + 1 = AgrNumber then '''' '
112 + ' when @agrnumbers = '''' then convert(varchar(20),AgrNumber) '
113 + ' when @tmpnum1 + 1 <> @tmpnum2 then '','' + convert(varchar(20),AgrNumber) '
114 + ' else ''-'' + convert(varchar(20),@tmpnum2) + '','' + convert(varchar(20),AgrNumber) '
115 + ' end, @tmpnum1 = @tmpnum2,@tmpnum2 = AgrNumber'
116 + ' from (select an.AgrNumber from AgreementNumber as an left join '+ @misdata +'.dbo.Agreement as agr'
117 + ' on an.AgrNumber = agr.AgrNumber and an.NumberPrefix = agr.NumberKind'
118 + ' where OutputCompany like ''' + @company + ''''
119 + ' and OutPutCompany is not NULL'
120 + ' and convert(varchar(6),ChangeDate,112) = ' + @sdate
121 + ' and (agr.Status = 4)'
122 + ' and NumberPrefix = ' + convert(varchar(8),@numberprefix)
123 + ' union select 9223372036854775807) as tmpt'
124 execute SP_executesql @strsql,N'@agrnumbers varchar(max) output',@agrnums output
125 IF(len(@agrnums) > 20)
126 UPDATE @Result SET DisuseAgrNumbers = left(@agrnums , len(@agrnums) - 20) WHERE ID = @i
127
128 SET @i=@i+1
129 END
130 SELECT NumberPrefix,UsedAgrNumbers,UsedAmount,UnUsedAgrNumbers,UnUsedAmount,DisuseAgrNumbers,DisuseAmount
131 FROM @Result
132 end--end if
133
134 GO
135
Code
1 -- =============================================
2 -- Create AgrNumber UnOutPut Amount procedure
3 -- 查询某日期的当前库存总数
4 -- =============================================
5
6 -- Drop stored procedure if it already exists
7 IF EXISTS (
8 SELECT *
9 FROM INFORMATION_SCHEMA.ROUTINES
10 WHERE SPECIFIC_SCHEMA = N'dbo'
11 AND SPECIFIC_NAME = N'AgrUnOutputStat'
12 )
13 DROP PROCEDURE dbo.AgrUnOutputStat
14 GO
15
16 CREATE PROCEDURE dbo.AgrUnOutputStat
17 @sdate varchar(8) = convert(varchar(8),GetDate(),112)--查询日期
18
19 AS
20 DECLARE @Result TABLE
21 (
22 ID int IDENTITY(1,1),
23 NumberPrefix smallint,--前缀
24 AgrNumbers varchar(max),--库存编号（连续编号用“-”间隔，非连续用“,”间隔）
25 Amount int--统计数
26 )
27 INSERT INTO @Result (NumberPrefix,Amount)
28 select NumberPrefix
29 ,Count(AgrNumber) as UnOutput--[未出库数/未分配给分公司(库存)]
30 from AgreementNumber
31 where OutputDate is null or convert(varchar(8),OutputDate,112) >= @sdate
32 group by NumberPrefix
33
34 DECLARE @i int
35 ,@j int
36 ,@tmpnum1 bigint--前2个的AgrNumber
37 ,@tmpnum2 bigint--前1个的AgrNumber
38 ,@agrnumbers nvarchar(max)
39
40 SELECT @i=1,@j=ISNULL(COUNT(1),0) FROM @Result
41 WHILE @i<=@j
42 BEGIN
43 SET @agrnumbers = ''
44 SET @tmpnum1 = -2
45 set @tmpnum2 = -2
46
47 --获取库存编号
48 select
49 @agrnumbers = @agrnumbers +
50 case
51 when @tmpnum2 + 1 = AgrNumber then ''
52 when @agrnumbers = '' then convert(varchar(20),AgrNumber)
53 when @tmpnum1 + 1 <> @tmpnum2 then ',' + convert(varchar(20),AgrNumber)
54 else '-' + convert(varchar(20),@tmpnum2) + ',' + convert(varchar(20),AgrNumber)
55 end
56 , @tmpnum1 = @tmpnum2,@tmpnum2 = AgrNumber
57 from
58 (
59 select AgrNumber
60 from AgreementNumber
61 where (OutputDate is null or convert(varchar(8),OutputDate,112) >= @sdate)
62 and NumberPrefix = (select NumberPrefix from @Result Where ID = @i)
63 union
64 select 9223372036854775807--max bigint
65 ) as tmpt
66
67 --更新编号
68 IF(len(@agrnumbers) > 20)
69 UPDATE @Result SET AgrNumbers = left(@agrnumbers , len(@agrnumbers) - 20) WHERE ID = @i
70 SET @i=@i+1
71 END
72 SELECT NumberPrefix,AgrNumbers,Amount FROM @Result
73
74 GO
75
76
77 -- =============================================
78 -- Create AgrNumber Output and UnClaimed Amount procedure
79 -- 查询某日期分公司出库但未领用数/分公司未有人领用(分公司库存)
80 -- 若参数 @company 为空结果为总数
81 -- =============================================
82
83 -- Drop stored procedure if it already exists
84 IF EXISTS (
85 SELECT *
86 FROM INFORMATION_SCHEMA.ROUTINES
87 WHERE SPECIFIC_SCHEMA = N'dbo'
88 AND SPECIFIC_NAME = N'AgrOutputUnClaimed'
89 )
90 DROP PROCEDURE dbo.AgrOutputUnClaimed
91 GO
92
93 CREATE PROCEDURE dbo.AgrOutputUnClaimed
94 @sdate varchar(8) = convert(varchar(8),GetDate(),112)--查询日期
95 @company varchar(8) = '%%'--查询分公司
96
97 AS
98 DECLARE @Result TABLE
99 (
100 ID int IDENTITY(1,1),
101 NumberPrefix smallint,
102 AgrNumbers varchar(max),
103 Amount int
104 )
105 INSERT INTO @Result (NumberPrefix,Amount)
106 select NumberPrefix
107 ,Count(AgrNumber) as OutputUnClaimed--[出库未领用数（分公司未有人领用）]
108 from AgreementNumber
109 where OutputCompany like @company
110 and (convert(varchar(8),OutputDate,112) < @sdate)
111 and (ClaimingDate is null or convert(varchar(8),ClaimingDate,112) >= @sdate)
112 group by NumberPrefix
113
114 DECLARE @i int
115 ,@j int
116 ,@tmpnum1 bigint
117 ,@tmpnum2 bigint
118 ,@agrnumbers nvarchar(max)
119
120 SELECT @i=1,@j=ISNULL(COUNT(1),0) FROM @Result
121 WHILE @i<=@j
122 BEGIN
123 SET @agrnumbers = ''
124 SET @tmpnum1 = -2
125 set @tmpnum2 = -2
126
127 --获取库存编号
128 select
129 @agrnumbers = @agrnumbers +
130 case
131 when @tmpnum2 + 1 = AgrNumber then ''
132 when @agrnumbers = '' then convert(varchar(20),AgrNumber)
133 when @tmpnum1 + 1 <> @tmpnum2 then ',' + convert(varchar(20),AgrNumber)
134 else '-' + convert(varchar(20),@tmpnum2) + ',' + convert(varchar(20),AgrNumber)
135 end
136 , @tmpnum1 = @tmpnum2,@tmpnum2 = AgrNumber
137 from
138 (
139 select AgrNumber
140 from AgreementNumber
141 where (convert(varchar(8),OutputDate,112) < @sdate)
142 and (ClaimingDate is null or convert(varchar(8),ClaimingDate,112) >= @sdate)
143 and OutputCompany like @company
144 and NumberPrefix = (select NumberPrefix from @Result Where ID = @i)
145 union
146 select 9223372036854775807--max bigint
147 ) as tmpt
148
149 --更新编号
150 IF(len(@agrnumbers) > 20)
151 UPDATE @Result SET AgrNumbers = left(@agrnumbers , len(@agrnumbers) - 20) WHERE ID = @i
152 SET @i=@i+1
153 END
154 SELECT NumberPrefix,AgrNumbers,Amount FROM @Result
155
156 GO
157
158
159 -- =============================================
160 -- Create AgrNumber Claimed and UnUsed Amount procedure
161 -- 查询某日期分公司人员领用但未使用数
162 -- 若参数 @company 为空结果为总数
163 -- =============================================
164
165 -- Drop stored procedure if it already exists
166 IF EXISTS (
167 SELECT *
168 FROM INFORMATION_SCHEMA.ROUTINES
169 WHERE SPECIFIC_SCHEMA = N'dbo'
170 AND SPECIFIC_NAME = N'AgrClaimedUnUsed'
171 )
172 DROP PROCEDURE dbo.AgrClaimedUnUsed
173 GO
174
175 CREATE PROCEDURE dbo.AgrClaimedUnUsed
176 @sdate varchar(8) = convert(varchar(8),GetDate(),112)--查询日期
177 @company varchar(8) = '%%'--查询分公司
178
179 AS
180 DECLARE @Result TABLE
181 (
182 ID int IDENTITY(1,1),
183 NumberPrefix smallint,
184 AgrNumbers varchar(max),
185 ClaimedUnUsedAmount int
186 )
187 INSERT INTO @Result (NumberPrefix,ClaimedUnUsedAmount)
188 select NumberPrefix
189 ,Count(AgrNumber) as ClaimedUnUsed--[领用未使用数]
190 from AgreementNumber
191 where convert(varchar(8),OutputDate,112) < @sdate--出库日期小于查询日期（在查询日期前已经被分公司领取）
192 and (convert(varchar(8),ClaimingDate,112) < @sdate)
193 and (UsedDate is null or convert(varchar(8),UsedDate,112) >= @sdate)
194 and OutputCompany like @company
195 group by NumberPrefix
196
197 DECLARE @i int
198 ,@j int
199 ,@tmpnum1 bigint
200 ,@tmpnum2 bigint
201 ,@agrnumbers nvarchar(max)
202
203 SELECT @i=1,@j=ISNULL(COUNT(1),0) FROM @Result
204 WHILE @i<=@j
205 BEGIN
206 SET @agrnumbers = ''
207 SET @tmpnum1 = -2
208 set @tmpnum2 = -2
209
210 --获取库存编号
211 select
212 @agrnumbers = @agrnumbers +
213 case
214 when @tmpnum2 + 1 = AgrNumber then ''
215 when @agrnumbers = '' then convert(varchar(20),AgrNumber)
216 when @tmpnum1 + 1 <> @tmpnum2 then ',' + convert(varchar(20),AgrNumber)
217 else '-' + convert(varchar(20),@tmpnum2) + ',' + convert(varchar(20),AgrNumber)
218 end
219 , @tmpnum1 = @tmpnum2,@tmpnum2 = AgrNumber
220 from
221 (
222 select AgrNumber
223 from AgreementNumber
224 where convert(varchar(8),OutputDate,112) < @sdate--出库日期小于查询日期（在查询日期前已经被分公司领取）
225 and (convert(varchar(8),ClaimingDate,112) < @sdate)
226 and (UsedDate is null or convert(varchar(8),UsedDate,112) >= @sdate)
227 and OutputCompany like @company
228 and NumberPrefix = (select NumberPrefix from @Result Where ID = @i)
229 union
230 select 9223372036854775807--max bigint
231 ) as tmpt
232
233 --更新编号
234 IF(len(@agrnumbers) > 20)
235 UPDATE @Result SET AgrNumbers = left(@agrnumbers , len(@agrnumbers) - 20) WHERE ID = @i
236 SET @i=@i+1
237 END
238 SELECT NumberPrefix,AgrNumbers,ClaimedUnUsedAmount FROM @Result
239
240 GO
241
242
243 -- =============================================
244 -- Create AgrNumber Claimed and UnUsed Amount With Claiming Date procedure
245 -- 查询某日期分公司人员领用但未使用情况(按协议书类别，领用日期分类汇总)
246 -- 若参数 @company 为空结果为总数
247 -- =============================================
248
249 -- Drop stored procedure if it already exists
250 IF EXISTS (
251 SELECT *
252 FROM INFORMATION_SCHEMA.ROUTINES
253 WHERE SPECIFIC_SCHEMA = N'dbo'
254 AND SPECIFIC_NAME = N'AgrClaimedUnUsedWithDate'
255 )
256 DROP PROCEDURE dbo.AgrClaimedUnUsedWithDate
257 GO
258
259 CREATE PROCEDURE dbo.AgrClaimedUnUsedWithDate
260 @sdate varchar(8) = convert(varchar(8),GetDate(),112)--查询日期
261 @company varchar(8) = '%%'--查询分公司
262
263 AS
264 DECLARE @Result TABLE
265 (
266 ID int IDENTITY(1,1),
267 NumberPrefix smallint,
268 AgrNumbers varchar(max),
269 ClaimingDate smalldatetime,
270 ClaimedUnUsedAmount int
271 )
272 INSERT INTO @Result (NumberPrefix,ClaimingDate,ClaimedUnUsedAmount)
273 select NumberPrefix,convert(varchar(10),ClaimingDate,102) as CDate
274 ,Count(AgrNumber) as ClaimedUnUsed--[领用未使用数]
275 from AgreementNumber
276 where OutputCompany like @company
277 and convert(varchar(8),OutputDate,112) < @sdate--出库日期小于查询日期（在查询日期前已经被分公司领取）
278 and (convert(varchar(8),ClaimingDate,112) < @sdate)
279 and (UsedDate is null or convert(varchar(8),UsedDate,112) >= @sdate)
280 group by NumberPrefix,convert(varchar(10),ClaimingDate,102)
281
282 DECLARE @i int
283 ,@j int
284 ,@tmpnum1 bigint
285 ,@tmpnum2 bigint
286 ,@agrnumbers nvarchar(max)
287 ,@numberprefix smallint
288 ,@claimingdate varchar(10)
289
290 SELECT @i=1,@j=ISNULL(COUNT(1),0) FROM @Result
291 WHILE @i<=@j
292 BEGIN
293 SET @agrnumbers = ''
294 SET @tmpnum1 = -2
295 set @tmpnum2 = -2
296
297 select @numberprefix = NumberPrefix,@claimingdate = convert(varchar(10),ClaimingDate,102) from @Result Where ID = @i
298
299 --获取库存编号
300 select
301 @agrnumbers = @agrnumbers +
302 case
303 when @tmpnum2 + 1 = AgrNumber then ''
304 when @agrnumbers = '' then convert(varchar(20),AgrNumber)
305 when @tmpnum1 + 1 <> @tmpnum2 then ',' + convert(varchar(20),AgrNumber)
306 else '-' + convert(varchar(20),@tmpnum2) + ',' + convert(varchar(20),AgrNumber)
307 end
308 , @tmpnum1 = @tmpnum2,@tmpnum2 = AgrNumber
309 from
310 (
311 select AgrNumber
312 from AgreementNumber
313 where convert(varchar(8),OutputDate,112) < @sdate
314 and (convert(varchar(8),ClaimingDate,112) < @sdate)
315 and (UsedDate is null or convert(varchar(8),UsedDate,112) >= @sdate)
316 and OutputCompany like @company
317 and NumberPrefix = @numberprefix
318 and convert(varchar(10),ClaimingDate,102) = @claimingDate
319 union
320 select 9223372036854775807--max bigint
321 ) as tmpt
322
323 --更新编号
324 IF(len(@agrnumbers) > 20)
325 UPDATE @Result SET AgrNumbers = left(@agrnumbers , len(@agrnumbers) - 20) WHERE ID = @i
326 SET @i=@i+1
327 END
328 SELECT NumberPrefix,AgrNumbers,ClaimingDate,ClaimedUnUsedAmount FROM @Result
329
330 GO
853
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
