1 Replies - 2561 Views - Last Post: 12 August 2012 - 06:11 PM

#1 Indrajitdasgupta  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 7
  • Joined: 27-December 10

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

Is This A Good Question/Topic? 0
  • +

Replies To: MSSQL store procedure

#2 thava  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 180
  • View blog
  • Posts: 1,606
  • Joined: 17-April 07

Re: MSSQL store procedure

Posted 12 August 2012 - 06:11 PM

use the substring function in the select query
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1