Can i change it in single query - Was pivot table in MS Access

• AMJ-458987

  SSC Eights!

  Points: 879

  More actions

  March 20, 2012 at 4:07 am

  #258924

  Hi All,

  Query in MS Access -

  TRANSFORM Count(Model2.UNIT) AS CountOfUNIT

  SELECT MDL.MODEL, MDL.MFG, MDL.SIZE, MDL.TYPE, MDL.DESC, MDL.YR2, MDL.TANK1 AS TankSize, MDL.TANK2 AS MPG

  FROM MDL LEFT JOIN Model2 ON (MDL.YR2 = Model2.MDLYEAR) AND (MDL.MODEL = Mode2.MODEL)

  WHERE (((MDL.SIZE)<>"UXAR") AND ((MDL.YR2) Between Format(Val(Format(Date(),"yy"))-1,"00") And Format(Val(Format(Date(),"yy"))+11,"00")))

  GROUP BY MDL.MODEL, MDL.MFG, MDL.SIZE, MDL.TYPE, MDL.DESC, MDL.YR2, MDL.TANK1, MDL.TANK2

  PIVOT Model.MDLYEAR;

  I am making it a stored procedure. Can i have any other better approach for this. Here is the stored procedure -

  CREATE PROCEDURE TEST

  -- Add the parameters for the stored procedure here

  AS

  BEGIN

  -- SET NOCOUNT ON added to prevent extra result sets from

  SET NOCOUNT ON;

  SET DATEFIRST 7

  BEGIN TRY

  If DATEPART(dw,GetDate()) = 2 -- Monday Only.

  BEGIN

  declare @sTempColName nvarchar(36)

  declare @cmdCase varchar(8000)

  declare @cmd varchar(8000)

  set @cmdCase = ''

  Declare curColName Insensitive Cursor

  For

  SELECT

  MDLYEAR

  from Model

  GROUP BY

  MDLYEAR

  For Read Only

  Open curColName

  Fetch Next From curColName Into @sTempColName

  While (@@Fetch_Status = 0)

  Begin

  set @cmdCase = @cmdCase + ', '

  SET @cmdCase = @cmdCase + 'COUNT(CASE WHEN MDLYEAR = ' + CHAR(39) + @sTempColName + CHAR(39) + ' THEN 1 ELSE null END) [' + LTRIM(RTRIM(@sTempColName)) + '] '

  Fetch Next From curColName Into @sTempColName

  End

  Close curColName

  Deallocate curColName

  set @cmd ='Select MDL.MODEL'

  set @cmd = @cmd+ ' ,MDL.MFG'

  set @cmd = @cmd+ ' , MDL.SIZE'

  set @cmd = @cmd+ ' , MDL.TYPE'

  set @cmd = @cmd+ ' , MDL.[DESC]'

  set @cmd = @cmd+ ' , MDL.YR2'

  set @cmd = @cmd+ ' , MDL.TANK1 AS TankSize'

  set @cmd = @cmd+ ' , MDL.TANK2 AS MPG'

  set @cmd = @cmd + @cmdCase

  set @cmd = @cmd+ ' From MDL '

  set @cmd = @cmd+ ' LEFT JOIN Model2'

  set @cmd = @cmd+ ' ON (MDL.YR2 = Model2.MDLYEAR)'

  set @cmd = @cmd+ ' AND (MDL.MODEL = Model2.MODEL)'

  set @cmd = @cmd+ ' where (MDL.YR2) between substring(convert(nvarchar,datepart(yy,getDate())-1),3,2) and substring(convert(nvarchar,datepart(yy,getDate())+11),3,2)'

  set @cmd = @cmd+ ' GROUP BY MDL.MODEL'

  set @cmd = @cmd+ ' , MDL.MFG, MDL.SIZE'

  set @cmd = @cmd+ ' , MDL.TYPE'

  set @cmd = @cmd+ ', MDL.[DESC]'

  set @cmd = @cmd+ ', MDL.YR2'

  set @cmd = @cmd+ ', MDL.TANK1'

  set @cmd = @cmd+ ', MDL.TANK2'

  EXEC (@cmd)

  END

  END TRY

  BEGIN CATCH

  SELECT ERROR_MESSAGE

  END CATCH

  End

  GO

  MJ:cool:

• Eugene Elutin

  SSC Guru

  Points: 59322

  More actions

  March 20, 2012 at 4:25 am

  #1461512

  ...

  Can I have any other better approach for this.

  ...

  O' Yes, You can! Please read this one:

  http://www.sqlservercentral.com/articles/Best+Practices/61537/

  Actually, you are not a newbie here...;-)

  _____________________________________________
  "The only true wisdom is in knowing you know nothing"
  "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
  (So many miracle inventions provided by MS to us...)

  How to post your question to get the best and quick help[/url]