How to merge the string ?
-
Hi,
My returns result set like this:
Case_No Case_Line_No No Remarks
---------------------------------------------------
1000003 10000 501 Remarks1
1000003 10000 502 Remarks2
1000003 10000 503 Remarks3
But I Want to Concatinate all remarks and I want in a single row.
How can I do it in single query?
Case_No Case_Line_No Remarks
--------------------------------------------------------------
1000003 10000 Remarks1Remarks2Remarks3
Thannks
K.Senthil Kumar
-
I don't have a solution with a single query. But if you are using SQL 2000, you can make use of a Scalar user defined function to concatenate the remarks column for the specific Case_No and Case_Line_No.
The function goes like this:
CREATE FUNCTION dbo.fnGetRemarks
(
@Case_No INT,
@Case_Line_No INT
)
RETURNS NVARCHAR(200)
AS
BEGIN
DECLARE @Remarks NVARCHAR(200)
SET @Remarks = ''
SELECT
@Remarks = @Remarks + Remarks
FROM
Table1
WHERE
Case_No = @Case_No
AND
Case_Line_No = @Case_Line_No
RETURN @Remarks
END
GO
Then the select Statement can use this function with the SELECT Clause as shown below:
SELECT
DISTINCT
Case_No,
Case_Line_No,
dbo.fnGetRemarks(Case_No, Case_Line_No) Remarks
FROM
Table1
GO
Regards,
Beulah Kingsly
-
Hi Beulah Kingsly,
Thx for immediate reply. But dbo.fnGetRemarks function will return only 200 charaters only rgt? I can't give fixed length return value.
Coz, I'm having so many records. Then try to change the return datatype as 'Text'.
I can't able to assign return data type as 'Text'. (Sql Server 2000 gives error)
Is there any other solution??
Thx
K.Senthil kumar
-
I don't think that there is any other solution. In the solution that I gave, you can increase the size to be 8000 and change the datatype to varchar.
regards,
Beulah Kingsly
-
If you want to use text for the output because the concatenation will be greater than 8000 then you will not be able to achieve this in a single query.
A solution using temp tables.
Assuming the results (first post) are in table #tempa
CREATE TABLE #tempb (Case_No int, Case_Line_No int, Remarks text)
INSERT INTO #tempb
(Case_No, Case_Line_No , Remarks)
SELECT DISTINCT Case_No,Case_Line_No, ''
FROM #tempa
DECLARE @Case_No int, @Case_Line_No int, @No int, @ptr binary(16), @Remarks varchar(100)
SELECT @Case_No = MIN(Case_No) FROM #tempb
SELECT @Case_Line_No = MIN(Case_Line_No) FROM #tempb WHERE Case_No = @Case_No
SELECT @No = MIN([No]) FROM #tempa WHERE Case_No = @Case_No AND Case_Line_No = @Case_Line_No
WHILE (@Case_No IS NOT NULL)
BEGIN
WHILE (@Case_Line_No IS NOT NULL)
BEGIN
SELECT @ptr = TEXTPTR(Remarks) FROM #tempb WHERE Case_No = @Case_No AND Case_Line_No = @Case_Line_No
WHILE (@No IS NOT NULL)
BEGIN
SELECT @Remarks = Remarks FROM #tempa WHERE Case_No = @Case_No AND Case_Line_No = @Case_Line_No AND [No] = @No
UPDATETEXT #tempb.Remarks @ptr NULL 0 @Remarks
SELECT @No = MIN([No]) FROM #tempa WHERE Case_No = @Case_No AND Case_Line_No = @Case_Line_No AND [No] > @No
END
SELECT @Case_Line_No = MIN(Case_Line_No) FROM #tempb WHERE Case_No = @Case_No AND Case_Line_No > @Case_Line_No
END
SELECT @Case_No = MIN(Case_No) FROM #tempb WHERE Case_No > @Case_No
END
SELECT * FROM #tempb
Far away is close at hand in the images of elsewhere.
Anon. -
This is what I see as your option.
Note: This is one of the few times you will see me use a cursor.
Replace tbl_Name with the name of your table.
------------------------------------Code Begins--------------------------------------------------------
SET NOCOUNT ON
IF OBJECT_ID('tempdb..#tmp_case') IS NOT NULL
DROP TABLE #tmp_case
CREATE TABLE #tmp_case (
Case_No INT NOT NULL,
Case_Line_No INT NOT NULL,
Remarks Varchar(7992) NULL -- This value is up to you I choose for 8k row max minus 8 bytes for 2 INTs.
)
INSERT #tmp_case SELECT DISTINCT Case_No, Case_Line_No, NULL FROM tbl_Name
DECLARE @CaseNo INT,
@CaseLineNo INT,
@Remarks VARCHAR(7992)
DECLARE cur_work CURSOR FOR
SELECT Case_No, Case_Line_No, Remarks FROM #tmp_case FOR UPDATE OF Remarks
OPEN cur_work
FETCH NEXT FROM cur_work
INTO @CaseNo, @CaseLineNo, @Remarks
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @Remarks = IsNull(@Remarks + ', ','') + Remarks FROM tbl_Name WHERE Case_No = @CaseNo AND Case_Line_No = @CaseLineNo ORDER BY [No]
UPDATE #tmp_case SET Remarks = @Remarks WHERE CURRENT OF cur_work
-- Get the next row.
FETCH NEXT FROM cur_work
INTO @CaseNo, @CaseLineNo, @Remarks
END
CLOSE cur_work
DEALLOCATE cur_work
SELECT * FROM #tmp_case
DROP TABLE #tmp_case
-
Thank you guys, for your help.Cheerz,
K.Senthil Kumar
-
Try something like
declare @Remarks varchar(1000)
select @Remarks =IsNull(@Remarks,'') + Remarks from .. where ...
select @Remarks
Regards
-
This will do it one statement using a function. Assuming the following table:
Create Table Testit (
Case_No char(10),
Case_Line_No int,
[No] int,
Remarks char(20))
go
Insert into Testit(Case_No, Case_Line_No, [No], Remarks)
values('1000003', 10000, 501, 'Remarks1')
go
Insert into Testit(Case_No, Case_Line_No, [No], Remarks)
values('1000003', 10000, 502, 'Remarks2')
go
Insert into Testit(Case_No, Case_Line_No, [No], Remarks)
values('1000003', 10000, 503, 'Remarks3')
go
Create Function dbo.ListRemarks(@Case_No as char(20), @Case_Line_No as int)
Returns varChar(500)
as
begin
Declare @Answer varchar(500),
@iNo int,
@iiNo int,
@iCount int
Set @iNo = 0
Set @iiNo = 0
SEt @Answer = ''
Select @iCount = Count(*) from Testit where Case_No = @Case_No and Case_Line_No = @Case_Line_No
while @iiNo @iNo
select @Answer = @Answer + Remarks from Testit where Case_No = @Case_No and Case_Line_No = @Case_Line_No and [No] = @iNo
set @iiNo = @iiNo + 1
End
Return @Answer
End
select cAse_No, Case_Line_No, dbo.ListRemarks(case_No, case_line_no)
from testit
Group by Case_No, Case_Line_No
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply