Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to add item No. in SELECT Expand / Collapse
Author
Message
Posted Thursday, March 7, 2013 10:29 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, May 4, 2013 7:55 AM
Points: 42, Visits: 74
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.
Post #1428124
Posted Thursday, March 7, 2013 10:45 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 12:13 PM
Points: 37, Visits: 1,629
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

Post #1428135
Posted Thursday, March 7, 2013 10:57 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, May 4, 2013 7:55 AM
Points: 42, Visits: 74
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
Post #1428145
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse