February 11, 2004 at 10:22 am
I have one table "CASE_CLAIMANT". WHICH HAS THREE FIELD
CASE_NUMBER CLAIMANT_NAME CLAIM_AMOUNT
1 JACK $1,000
1 MACK $2,000
1 MIKE $3,000
2 KRISH $4,000
Is there a way to produce result as shown below by writing a single query using grouping on CASE_NUMBER and Concatenating CLAIMANT_NAME seperated by comma or any other delimeter.
CASE_NUMBER CLAIMANT_NAME CLAIM_AMOUNT
1 JACK, MACK, MIKE $6,000
2 KRISH $4,000
I have tried so many options as well as search over the web but didn't get it.
In our reporting application we have to produce result as shown about and for that right now we are opeting one recordset to get claim_amount group by case_number and within this loop opening another recordset to get all the CLAIMANT_NAME and looping thru it to get the result.
I dont want to open another recordset within the loop and looking for the result just in one Query.
I will appreciate for your help.
Thanks
February 11, 2004 at 12:01 pm
I don't think there is any way to do this in a single query. I would select into a temporary table grouping on CASE_NUMBER and getting the min CLAIMANT_NAME, summing the CLAIM_AMOUNT and getting a count of each group. Then I would loop through all of the rows where the count was greater than 1 and update the CLAIMANT_NAME.
-Kevin Williams
February 12, 2004 at 1:07 am
If the server is MS SQL 2000, this UDF may help:
CREATE FUNCTION dbo.udfConcatenate_Claimant_Names (@case_number int)
RETURNS varchar(1000)
AS
BEGIN
DECLARE @ret_val varchar(1000)
SET @ret_val = ''
SELECT
@ret_val = CASE @ret_val
WHEN '' THEN CLAIMANT_NAME
ELSE @ret_val + ', ' + CLAIMANT_NAME
END
FROM
CASE_CLAIMANT
WHERE
(CASE_NUMBER = @case_number)
ORDER BY
CLAIMANT_NAME
RETURN (@ret_val)
END
GO
It does the same thing (looping through the range of rows), but you don't need a second recordset, because the whole job is done on the server, and you can use a single query to get the report:
SELECT
CASE_NUMBER, dbo.udfConcatenate_Claimant_Names (CASE_NUMBER) AS CLAIMANT_NAME,
SUM(CLAIM_AMOUNT) AS CLAIM_AMOUNT
FROM
CASE_CLAIMANT
GROUP BY
CASE_NUMBER, dbo.udfConcatenate_Claimant_Names (CASE_NUMBER)
ORDER BY
CASE_NUMBER
Of course, index on CASE_NUMBER will help the function to run efficiently.
Regards,
Goce Smilevski.
February 12, 2004 at 10:26 am
Thanks g_smilevski!
That really hepled me alot!
February 13, 2004 at 8:51 am
Thanks, i appreciate your help
g_smilevski
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply