MSSQL store procedure

Posted 08 August 2012 - 03:46 AM

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 below 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 below table
Here I am changing the cell value into coloumn

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 get the above table 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

What should I change in my store Procedure to get the above table

Thanks