merge multiple rows into one row

Posted by Shivanichhabra4u under Sql Server on 10/22/2008 | Views : 3236 | Status : [Member] | Replies : 1
qUESTION IS :SUPPOSE I HAVE TABLE LIKE AS SHOWN BELOW

id CONTACT NO CONTACT TYPE
---------------------------------
1 95 2
1 96 2
1 97 5



RESULT SHOULD BE LIKE


id CONTACT NO CONTACT TYPE
---------------------------------
1 95,96 2
1 97 5




Responses

Posted by: Rajeevkrg1 on: 10/23/2008 [Member] Starter

Up
0
Down
/* 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

Login to post response