Click here to Skip to main content
16,021,293 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
using the below query i am getting the result as
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 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 ORDER BY ID

output:

SQL
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


how to get output Member_ID values in ASCENDING order

ex:
SQL
ID Member_ID
18 39,39,39,39,51
25 51
97 41
102 41,51
113 41,116
155 39,217
888 41,49,68,147,148,149,151,175
662 202,202
841 3,3,116
Posted
Updated 8-Nov-12 18:14pm
v2

1 solution

use order by in sub query,
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 Savings_Account_Applicant where ID =saa.ID order by Applicant_Member_Id for xml path('')) as Applicant_Member_Ids
    from Savings_Account_Applicant saa
    group by ID
) as Savings_Account ORDER BY ID


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
CREATEFunction [dbo].[fn_Splitter] (@IDs Varchar(100) )  
Returns @Tbl_IDs Table  (ID Int)  As  
Begin 
 -- Append comma
 Set @IDs =  @IDs + ',' 
 -- Indexes to keep the position of searching
 Declare @Pos1 Int
 Declare @pos2 Int
  -- Start from first character 
 Set @Pos1=1
 Set @Pos2=1
 While @Pos1<Len(@IDs)
 Begin
  Set @Pos1 = CharIndex(',',@IDs,@Pos1)
  Insert @Tbl_IDs Select  Cast(Substring(@IDs,@Pos2,@Pos1-@Pos2) As Int)
  -- Go to next non comma character
  Set @Pos2=@Pos1+1
  -- Search from the next charcater
  Set @Pos1 = @Pos1+1
 End 
 Return
End

Happy Coding!
:)
 
Share this answer
 
v3
Comments
sk. maqdoom ali 8-Nov-12 23:48pm    
Thank u Madam
Aarti Meswania 8-Nov-12 23:53pm    
welcome :)
sk. maqdoom ali 9-Nov-12 0:19am    
hi, using u r query i written the following function that checks where the input given is existed in that computed table or not. if it exists it returns 'Already Exists' else 'Available'.
But here i will pass input @Ids in any order ex:@Ids = '41,56,87,34'
from the above ex: input @Ids = '41,56,87,34' the string must be taken input in ascending order ie;'34,41,56,87'.
so how we can convert the @Ids value in ascending order in that function.

sk. maqdoom ali 9-Nov-12 0:20am    
ALTER FUNCTION [dbo].[Check_MemberIDCombination]
(
@Ids VARCHAR(1000)
)
RETURNS VARCHAR(20)
AS
BEGIN

DECLARE @Msg VARCHAR(20);
declare @count int

DECLARE @Computed table(id nvarchar(1000),Member_Id nvarchar(1000))
insert into @computed
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 order by Applicant_Member_Id for xml path('')) as Applicant_Member_Ids
from Savings_Account_Applicant saa
group by ID
) as Savings_Account ORDER BY ID

set @count = ( select count(*) from @Computed where Member_Id=@Ids)

if @count>0
begin

SET @Msg = 'Already Exists'
end
ELSE
begin
SET @Msg = 'Available'

END
RETURN @Msg
end
Aarti Meswania 9-Nov-12 0:33am    
two process on @Ids
split
and
merge

example.
select @Ids=(select convert(varchar,Id) + ',' from ((select Id from dbo.Fn_Splitter(@Ids))) as tmp order by Id for xml path (''))
select @Ids= substring(@Ids,1,Len(@Ids))
after that pass @Ids for comparison in computed Query

Create function in your database
given in Answer see updated solution

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900