MS SQL查询存储过程 [英] MS SQL Query for Store Procedure
本文介绍了MS SQL查询存储过程的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
大家好
我有一个像下面的表格
Hi All
I have a table like below
Rack Cell Qty
A1 1 50
A2 2 20
A3 3 70
B1 1 80
B2 3 90
C1 1 56
C3 3 77
C4 4 400
D1 5 55
D2 6 66
执行以下存储过程后
After executing the following Store Procedure
ALTER PROCEDURE [dbo].[SP_TestForAccess]
AS
BEGIN
DECLARE @query NVARCHAR(4000)
DECLARE @cols NVARCHAR(2000)
SELECT @cols = COALESCE(@cols +',['+ cell +']','['+ cell +']') FROM (SELECT DISTINCT cell FROM TestForAccess)as T
PRINT @cols
SET @query ='select rack,'+@cols+' FROM (select rack,cell,qty FROM TestForAccess)AS D pivot(sum(qty) for cell in ('+@cols+')) as P;'
--EXEC SP_EXECUTESQL @query
PRINT @query
EXECUTE(@query)
END
我正在获取下表
在这里,我将单元格值更改为列
I am getting the following table
Here I am changing the cell value into columns
Rack 1 2 3 4 5 6
A1 50 NULL NULL NULL NULL NULL
A2 NULL 20 NULL NULL NULL NULL
A3 NULL NULL 70 NULL NULL NULL
B1 80 NULL NULL NULL NULL NULL
B2 NULL NULL 90 NULL NULL NULL
C1 56 NULL NULL NULL NULL NULL
C3 NULL NULL 77 NULL NULL NULL
C4 NULL NULL NULL 400 NULL NULL
D1 NULL NULL NULL NULL 55 NULL
D2 NULL NULL NULL NULL NULL 66
现在,我的目的是像下面那样将组归入机架"列中.
Now my intention is to make group to the Rack column like below
Rack 1 2 3 4 5 6
A1 50 NULL NULL NULL NULL NULL
A A2 NULL 20 NULL NULL NULL NULL
A3 NULL NULL 70 NULL NULL NULL
B1 80 NULL NULL NULL NULL NULL
B B2 NULL NULL 90 NULL NULL NULL
C1 56 NULL NULL NULL NULL NULL
C C3 NULL NULL 77 NULL NULL NULL
C4 NULL NULL NULL 400 NULL NULL
D1 NULL NULL NULL NULL 55 NULL
D D2 NULL NULL NULL NULL NULL 66
我应该如何更改上述存储过程以使其像上表那样
感谢All
What should I change the above store procedure to get like the above table
Thanks to All
推荐答案
请参见下面给出的示例
see example given below
select RackGrp as RackGrp, Rack as Rack from
(
select 'a1' as Rack,substring('a1',1,1) as RackGrp
union all
select 'a2' as Rack,substring('a2',1,1) as RackGrp
union all
select 'b1' as Rack,substring('b1',1,1) as RackGrp
) as a
祝您编码愉快!
:)
Happy Coding!
:)
这篇关于MS SQL查询存储过程的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文