Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-Server
Hi,
 
How to count the row number for the below table which is having a combination of same numbers when we are giving multiple values as input.
@Member_ID = 39,51,---
 
ID                 Member_ID
18	         39,39,39,51,39
25	           51
97	           41
102	         41,51
113	         41,116
155	         39,217
888	         41,49,147,149,151,148,175,68
662	         202,202
841	         116,3,3
The above combination is generated by writing the following query -
select ID, substring(Applicant_Member_Ids,1,len(Applicant_Member_Ids)-1) from
(
    select ID ,
    (select convert(varchar, Applicant_Member_Id) + ','  as [text()] from Savings_Account_Applicant where ID =saa.ID  for xml path(''))  as Applicant_Member_Ids
    from Savings_Account_Applicant saa
    group by ID
) as Savings_Account;
Posted 8-Nov-12 1:55am
Edited 8-Nov-12 20:32pm
Maciej Los136.2K
v4
Comments
Aarti Meswania at 9-Nov-12 2:36am
   
that solution not worked?
 
and you was used count() then now why you want rownumber()?
Maciej Los at 9-Nov-12 2:44am
   
What do you want to count: ID's or MemberID's or MemberID's for each ID?
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

First of all, see my comment to your post.
 
If you want to count ID's
SELECT COUNT(ID) AS [CountOfID's]
FROM YourTable
or
SELECT COUNT(DISTINCT ID) AS [CountOfID's]
FROM YourTable
 
Analogically, if you want to count MemebrID's
SELECT COUNT(MemberID) AS [CountOfMemberID's]
FROM YourTable
or
SELECT COUNT(DISTINCT MemberID) AS [CountOfMemberID's]
FROM YourTable
 
Finally, if you want to count MemberID's for each ID
SELECT ID, COUNT(MemberID) AS [CountOfMemberID's]
FROM YourTable
or
SELECT ID, COUNT(DISTINCT MemberID) AS [CountOfMemberID's]
FROM YourTable
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Apply row number as below
select RowNo,Id,A_Ids from
(select Row_Number() over(order by Applicant_Member_Ids) as RowNo, ID, substring(Applicant_Member_Ids,1,len(Applicant_Member_Ids)-1) as A_Ids from
(
    select ID ,
    (select convert(varchar, Applicant_Member_Id) + ','  as [text()] from Savings_Account_Applicant where ID =saa.ID  for xml path(''))  as Applicant_Member_Ids
    from Savings_Account_Applicant saa
    group by ID
) as Savings_Account
) as temp order by RowNo;
Happy Coding!
Smile | :)
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



Advertise | Privacy | Mobile
Web04 | 2.8.140827.1 | Last Updated 9 Nov 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100