function which displays the combination of values with comma separated between them

Question

4.00/5 (1 vote)

See more:

hi, for my application

i have a table with two columns ID and Applicant_Member_Id having data as

ID Applicant_Member_Id

117 1
117 2
186 3
186 4
223 5
224 6
556 7
556 8
186 9
-
-
-
i want a function that which returns the output as
117 1,2
186 3,4
556 7,8
223 5
224 6

Posted 7-Nov-12 17:36pm

sk. maqdoom ali

Updated 7-Nov-12 17:41pm

v2

Add a Solution

2 solutions

Solution 2

Try this...

SQL

DECLARE @ApplicantTable TABLE
(
  ID INT,
  ApplicantId INT
)

INSERT INTO @ApplicantTable VALUES
(117,1),
(117 ,2),
(186 ,3),
(186 ,4),
(223, 5),
(224, 6),
(556, 7),
(556, 8),
(186, 9)

SELECT ID,STUFF(( SELECT ',' + CAST(ApplicantId AS VARCHAR) FROM @ApplicantTable 
WHERE ID = A.Id FOR XML PATH('') ),1,1,'')Ids
FROM @ApplicantTable A GROUP BY ID

And to get the rownumber according to the combination....

SQL

SELECT ID,ROW_NUMBER() OVER(ORDER BY Ids) RowNum,IDs FROM (
SELECT ID,STUFF(( SELECT ',' + CAST(ApplicantId AS VARCHAR) FROM @ApplicantTable 
WHERE ID = A.Id FOR XML PATH('') ),1,1,'')Ids
FROM @ApplicantTable A GROUP BY ID ) A

Thank you

Posted 7-Nov-12 18:46pm

damodara naidu betha

Updated 7-Nov-12 19:00pm

v2

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Aarti Meswania · Accepted Answer · 2012-11-07T17:53:00

Solution 1

There is not a function for this
you can use query that will combine value using FOR XML PATH('') Inbuilt Function of SQL

SQL

select ID, substring(Applicant_Member_Ids,1,len(Applicant_Member_Ids)-1) from
(
    select ID ,
    (select convert(varchar, Applicant_Member_Id) + ','  as [text()] from YourTable where ID =a.ID  for xml path(''))  as Applicant_Member_Ids
    from YourTable a
    group by ID
) as temp

Happy Coding!
:)

Posted 7-Nov-12 17:53pm

Aarti Meswania

Updated 7-Nov-12 17:55pm

v2

Comments

damodara naidu betha 7-Nov-12 23:58pm

5+

Aarti Meswania 7-Nov-12 23:59pm

Thank you!
:)

sk. maqdoom ali 8-Nov-12 0:13am

thank u,
For my banking application i will pass many number of Applicant_Member_Id 's and my requirement is if i selected 41,42 for one JointAccount then for another JointAccount i will restrict the same combination which i have given before ie,41,42
i can use 41,43 or 42,43 like this.
i want to restrict the combination which is already repeated

Aarti Meswania 8-Nov-12 0:19am

yes you can
if you mean 41,42 should not repeat
then use Row_Number function over Applicant_Member_Ids and display Which row_Number is equal to 1

sk. maqdoom ali 8-Nov-12 0:43am

i written this query but i am getting the Row_Number which shows invidually not by combination ,
SELECT
ROW_NUMBER() over(order by id) AS Row, Applicant_Member_Id ,ID
FROM Savings_Account_Applicant where Applicant_Member_Id in(39,51)

Aarti Meswania 8-Nov-12 0:47am

please give sample input output
because I can't get you,
why you wrote "where Applicant_Member_Id in(39,51)" ?
and you should do Rownumber over Applicant_Member_Ids which have values with comma

sk. maqdoom ali 8-Nov-12 1:12am

ex: i have a joint account id =101 having
Applicant_member_Id=10,11,12
now i am giving Applicant_member_Id = 10 as input i have to check that 10 is existed or not , like this i will give 11 as input i have to check that 11 is existed or not ,
if it existed i will return 1 else return 2
here the condition is no two or more same combination of Applicant_member_Id's
is not valid for another JointAccount. i have to check this condition before insertion

Aarti Meswania 8-Nov-12 1:24am

okay you mean to say

Applicant_Member_Id = 1
Now, It will be unique in your table

Right case
101 1
101 2

Wrong case is
101 1
101 2
102 1 // you not want to allow this

if it is then you have to check only
select count(*) from your table where Applicant_member_Id=1

if it return 0 then it can be added
else
Applicant_member_Id have already included in a join account no. is 101 you can not add it in another join account 102.

Was it Right?

sk. maqdoom ali 8-Nov-12 1:33am

yes ,
it return 0 then it can be added else Applicant_member_Id have already included in a join account no. is 101 you can not add it in another join account 102

Aarti Meswania 8-Nov-12 1:38am

okay then it is solved
just use this query
select count(*) from your table where Applicant_member_Id=1

as i mentioned in previous comment
it is simply you can make your column Applicant_member_Id unique in table for more security in multi-user environment.

mark it as solution if it solved your problem
:)

sk. maqdoom ali 8-Nov-12 2:57am

if when we are inserting one or more Applicant_member_Id's
for ex:39,41,116 as input then ??
i have to check entire this combination 39,41,116 is already existed for any particular ID in the table.
if the combination is existed then we display "Already Existed' else 'available'