/* Here is the one way to get the merged result.
In question Merging condition is not clear , i am assuming if ID and ContactType get same then that contactNo must be merged.
so i proceed with that logic.....
*/
Create Table myTest
(
Id Int,
CNo varchar(10),
Ctype int
)
-- delete myTest
Insert into myTest Values(1,'A',1)
Insert into myTest Values(2,'B',2)
Insert into myTest Values(2,'C',2)
Insert into myTest Values(2,'D',2)
-- select * from dbo.fnMergeColValues() -- for viewing
-- Fnction output
--Creating Function that reurns a tables result
--that contains merged values
--(W) Rajeev Kumar 23.10.2008
Create Function fnMergeColValues()
Returns @Table01 table( Id Int,
CNo varchar(1000),
Ctype int
)
begin
Declare @CNo as varchar(15)
Declare @cType as Int
Declare @Id as Int
Declare @MergedColValues Varchar(1000)
Declare CurName Cursor for Select Distinct Id,Ctype
From myTest
open CurName
Fetch Next from CurName into @Id,@cType
While @@fetch_status = 0
begin
set @MergedColValues = ''
Declare InnerCurs Cursor for Select CNo
From myTest
Where Id = @Id
And Ctype = @cType
Open InnerCurs
Fetch Next from InnerCurs into @CNo
While @@fetch_status = 0
Begin
set @MergedColValues = @MergedColValues + @CNo + ','
Fetch Next from InnerCurs into @CNo
end
Close InnerCurs
Deallocate InnerCurs
if len(@MergedColValues) > 1
set @MergedColValues = left(@MergedColValues,len(@MergedColValues)-1)
insert into @Table01
values(@id,@MergedColValues,@cType)
Fetch Next from CurName into @Id,@cType
end
Close CurName
Deallocate CurName
return
end
Shivanichhabra4u, if this helps please login to Mark As Answer. | Alert Moderator