Need help using While

• Sharul Nizam

  Ten Centuries

  Points: 1302

  More actions

  January 21, 2010 at 8:04 am

  #140725

  My table and row as follow,

  declare @tBusType TABLE

  (

  TrnxID int identity(1,1),

  [BusTypeCode] [nvarchar](50) NOT NULL,

  [BusTypeDesc] [nvarchar](50) NULL,

  [nColLeft] [smallint] NULL,

  [nColRight] [smallint] NULL,

  [nRow] [smallint] NULL

  )

  insert into @tBusType(BusTypeCode,BusTypeDesc,nColLeft,nColRight,nRow)

  values('STD26','STD26',1,2,9);

  insert into @tBusType(BusTypeCode,BusTypeDesc,nColLeft,nColRight,nRow)

  values('STD48','STD48',2,2,12);

  insert into @tBusType(BusTypeCode,BusTypeDesc,nColLeft,nColRight,nRow)

  values('STD30','STD30',1,2,11);

  /*BusTypeCode is a unique*/

  declare @tBusSeatInXY table

  (

  TrnxID int identity(1,1),

  BusCd char(10) not null,

  col_X tinyint null,

  row_Y tinyint null,

  seatDesc char(4) null

  )

  declare @colLeft as tinyint

  declare @colRight as tinyint

  declare @col_X as tinyint

  declare @row_Y as tinyint

  declare @gBusTypCd as varchar(50)

  set @gBusTypCd='STD26'

  select @col_X=nColLeft+nColRight,@row_Y=nRow from @tBusType where BusTypeCode=@gBusTypCd

  declare @i tinyint;

  declare @j-2 tinyint;

  set @i = 1;

  while @i <= @row_Y

  begin

  set @j-2 = 1;

  while @j-2 <=@col_X

  begin

  insert into @tBusSeatInXY(BusCd,col_X,row_Y) values(@gBusTypCd,@j,@i)

  set @j-2 = @j-2 + 1;

  end

  set @i = @i + 1;

  end

  My SQL result as follow,

  select * from @tBusType

  1STD26STD26129

  2STD48STD482212

  3STD30STD301211

  select * from @tBusSeatInXY

  1STD26 11NULL

  2STD26 21NULL

  3STD26 31NULL

  4STD26 12NULL

  5STD26 22NULL

  6STD26 32NULL

  7STD26 13NULL

  8STD26 23NULL

  9STD26 33NULL

  10STD26 14NULL

  11STD26 24NULL

  12STD26 34NULL

  13STD26 15NULL

  14STD26 25NULL

  15STD26 35NULL

  16STD26 16NULL

  17STD26 26NULL

  18STD26 36NULL

  19STD26 17NULL

  20STD26 27NULL

  21STD26 37NULL

  22STD26 18NULL

  23STD26 28NULL

  24STD26 38NULL

  25STD26 19NULL

  26STD26 29NULL

  27STD26 39NULL

  How to make it my below SQL dynamic

  declare @gBusTypCd as varchar(50)

  set @gBusTypCd='STD26'

  select @col_X=nColLeft+nColRight,@row_Y=nRow from @tBusType where BusTypeCode=@gBusTypCd

  declare @i tinyint;

  declare @j-2 tinyint;

  set @i = 1;

  while @i <= @row_Y

  begin

  set @j-2 = 1;

  while @j-2 <=@col_X

  begin

  insert into @tBusSeatInXY(BusCd,col_X,row_Y) values(@gBusTypCd,@j,@i)

  set @j-2 = @j-2 + 1;

  end

  set @i = @i + 1;

  end

  So, my While Loop can write the rest row of STD48 and STD30?

  I'm stuck

• LutzM

  SSC Guru

  Points: 107049

  More actions

  January 21, 2010 at 11:32 am

  #1106872

  Why not using a solution that doesn't use any while loops at all and do it all in one path?

  ;with cte

  AS

  (

  SELECT

  BusTypeCode,

  n1 % (ncolLeft+nColRight) +1 AS Col_X,

  row_number() over(partition BY BusTypeCode,n1 % (ncolLeft+nColRight) ORDER BY n1 % nRow ) AS row_Y

  FROM @tBusType

  CROSS apply

  ( SELECT n n1

  FROM tally

  WHERE n<=(nRow * (ncolLeft+nColRight))

  ) row

  )

  SELECT

  row_Number() over(partition BY BusTypeCode ORDER BY row_Y,Col_X ) AS TrnxID,

  * FROM cte

  ORDER BY BusTypeCode, row_Y,Col_X

  If you don't have a Tally table yet you should get it by following the related link in my signature. It's on sale this week!!!! (Usually you can purchase it for 0.00USD but this week it's FOR FREE!! :w00t:

  Note: have a look at the article and you'll see that I'm just kidding regarding the price... 😉

  Edit: code replaced with a better performing solution... (ashamed of keeping the old version available...) :blush:

  ---

  
  Lutz
  A pessimist is an optimist with experience.

  How to get fast answers to your question[/url]
  How to post performance related questions[/url]
  Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

• Sharul Nizam

  Ten Centuries

  Points: 1302

  More actions

  January 21, 2010 at 4:17 pm

  #1107004

  Tally table really great ...

• Jeff Moden

  SSC Guru

  Points: 1004641

  More actions

  January 21, 2010 at 11:06 pm

  #1107102

  lmu92 (1/21/2010)

  ---

  Why not using a solution that doesn't use any while loops at all and do it all in one path?

  Well done, Lutz!

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• Jeff Moden

  SSC Guru

  Points: 1004641

  More actions

  January 21, 2010 at 11:08 pm

  #1107103

  Sharul Nizam (1/21/2010)

  ---

  My table and row as follow,

  My hat's off to you, Sharul... you really made it easy for folks to help you with the way you posted. Well done!

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)