March 7, 2013 at 10:29 am
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.
March 7, 2013 at 10:45 am
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
March 7, 2013 at 10:57 am
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
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply