How to add item No. in SELECT

• RZ52

  Mr or Mrs. 500

  Points: 536

  More actions

  March 7, 2013 at 10:29 am

  #394335

  Hello,

  I have a table like :

  DECLARE @test1 TABLE (C_NAME varchar(10), REQ_ITEM VARCHAR(5))

  INSERT INTO @test1(C_NAME,REQ_ITEM) VALUES('John', 'Item1')

  INSERT INTO @test1(C_NAME,REQ_ITEM) VALUES('John', 'Item2')

  INSERT INTO @test1(C_NAME,REQ_ITEM) VALUES('Jake', 'Item2')

  INSERT INTO @test1(C_NAME,REQ_ITEM) VALUES('Jake', 'Item3')

  INSERT INTO @test1(C_NAME,REQ_ITEM) VALUES('Jake', 'Item4')

  INSERT INTO @test1(C_NAME,REQ_ITEM) VALUES('Jake', 'Item5')

  INSERT INTO @test1(C_NAME,REQ_ITEM) VALUES('Joe', 'Item1')

  INSERT INTO @test1(C_NAME,REQ_ITEM) VALUES('Joe', 'Item5')

  INSERT INTO @test1(C_NAME,REQ_ITEM) VALUES('Jane', 'Item3')

  How can I show something like :

  C_NAME ITEM_NO REQ_ITEM

  -------------------------------------

  John 01 Item1

  John 02 Item2

  Jake 01 Item2

  Jake 02 Item3

  Jake 03 Item4

  Jake 04 Item5

  Joe 01 Item1

  Joe 02 Item5

  Jane 01 Item3

  Thanks in advance.

• Deque

  Mr or Mrs. 500

  Points: 589

  More actions

  March 7, 2013 at 10:45 am

  #1594588

  Does this do what you are looking for?

  SELECT C_NAME, ROW_NUMBER() OVER(PARTITION BY C_NAME ORDER BY C_NAME ASC) AS ITEM_NO, REQ_ITEM FROM @test1

• RZ52

  Mr or Mrs. 500

  Points: 536

  More actions

  March 7, 2013 at 10:57 am

  #1594597

  Deque (3/7/2013)

  ---

  Does this do what you are looking for?

  SELECT C_NAME, ROW_NUMBER() OVER(PARTITION BY C_NAME ORDER BY C_NAME ASC) AS ITEM_NO, REQ_ITEM FROM @test1

  Yep, It worked.

  Thanks Deque