MS SQL查询存储过程 [英] MS SQL Query for Store Procedure

问题描述

大家好
我有一个像下面的表格

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屋！