March 29, 2007 at 3:45 pm
Hi pple
There is a TSQL query i want to build to Group or cancatenate attributes of multiple rows which is related to similar entry Dates, and copy this output result to a new table created.
heres an example of the output table:
Date entered result
01/jan/2000 A
01/jan/2000 c
02/feb/2001 B
02/feb/2001 e
02/feb/2001 y
Now the following table represents the Desired result i want from the table above to be copied to a new table:
Date entered result
01/jan/2000 A/C
02/Feb/2001 B/E/Y
thx for any assistance given
March 29, 2007 at 4:58 pm
Somebody asked the same question earlier today. See the answer here:
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=354813
-mr
March 29, 2007 at 5:40 pm
Just tried that method, but still not gettin the result
any other suggestion please?
March 29, 2007 at 5:55 pm
March 29, 2007 at 8:02 pm
i'm trying the following
use KPaids
go
drop function dbo.fn_GetARVList
go
CREATE FUNCTION dbo.fn_GetARVList
(
@KP char(10)
,@DS datetime
)
RETURNS SQL_variant
AS
BEGIN
DECLARE @buffer SQL_variant
SELECT
@buffer = IsNull(@buffer + ',', '') + ARV
FROM
dbo.Registry_ARV
WHERE
KPAIDS_NO = @KP
AND Date_Seen = @DS
RETURN @buffer
END
GO
--Then use the code:
SELECT DISTINCT
KPAIDS_NO
, Date_Seen
,dbo.fn_GetARVList(KPAIDS_NO, Date_Seen)
FROM
dbo.Registry_ARV
and i get this error
Server: Msg 403, Level 16, State 1, Procedure fn_GetARVList, Line 13
Invalid operator for data type. Operator equals add, type equals sql_variant.
Server: Msg 208, Level 16, State 1, Line 5
Invalid object name 'dbo.fn_GetARVList'.
March 29, 2007 at 8:08 pm
datatypes for columns are as follows:
KPAIDS_NO char(10)
Date_Seen datetime
ARV int
March 29, 2007 at 8:34 pm
1st, you declared @buffer as SQL_variant. It must be nvarchar(4000).
2nd, you cannot add string value to either int or sql_variant.
You must convert ARV to nvarchar before adding it to ','
And avoid using DISTINCT.
SELECT KPAIDS_NO, Date_Seen
,dbo.fn_GetARVList(KPAIDS_NO, Date_Seen)
FROM dbo.Registry_ARV
GROUP BY KPAIDS_NO, Date_Seen
_____________
Code for TallyGenerator
March 29, 2007 at 8:57 pm
ok thx man
March 30, 2007 at 11:17 am
Ok it worked thx alot again, however i noticed a lot blank spaces seperating each variables.
ex>> apple ,cheese
peach ,bread
how can i rid these blank spaces?
March 30, 2007 at 12:21 pm
Ltrim(Rtrim(character_expression))
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply