Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-Server
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
Edited 7-Nov-12 17:41pm
v2
Rate this: bad
good
Please Sign up or sign in to vote.

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
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!
Smile | :)
  Permalink  
v2
Comments
damodara naidu betha at 7-Nov-12 23:58pm
   
5+
Aarti Meswania at 7-Nov-12 23:59pm
   
Thank you!
:)
sk. maqdoom ali at 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 at 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 at 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 at 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 at 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 at 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 at 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 at 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 at 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'
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

Try this...
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....
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
  Permalink  
v2

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

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 725
1 OriginalGriff 385
2 CPallini 240
3 PIEBALDconsult 150
4 Magic Wonder 131
0 OriginalGriff 5,835
1 Sergey Alexandrovich Kryukov 5,198
2 CPallini 4,740
3 George Jonsson 3,167
4 Gihan Liyanage 2,470


Advertise | Privacy | Mobile
Web02 | 2.8.140916.1 | Last Updated 8 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