Hi ....
Create a UDF I have tested it on sql server 2000
CREATE FUNCTION dbo.UDF_Messagedetail1
( @MsgID char(1) )
RETURNS varchar(1000)
AS
BEGIN
DECLARE @MsgDetail varchar(1000), @Delimiter char
SET @Delimiter = '+'
SELECT @MsgDetail = COALESCE(@MsgDetail + @Delimiter, '') + strPQ
FROM
(SELECT MSG_ID,Message,'('' * ' + LTRIM(Message) + ')' AS strPQ
FROM dbo.MSG_TAB inner join MSG_DET on [ID] = @MsgID) derived
WHERE MSG_ID=@MsgIDRETURN ( SELECT REPLACE(@MsgDetail,')+(',') + (') AS [Message_Detail])
END
Once you have done that run sql statement
select distinct(MSG_ID), dbo.UDF_Messagedetail1(MSG_ID) AS Message
From dbo.MSG_TAB
You will get the results
1 (' * MSG1 ) + (' * MSG2 ) + (' * MSG3 )
2 (' * MSG1 ) + (' * MSG2 ) + (' * MSG3 )
3 (' * MSG1 ) + (' * MSG2 ) + (' * MSG3 )
4 (' * MSG1 )
Test Tables
MSG_TAB Table
ID Message
1 MSG1
1 MSG2
1 MSG3
2 MSG1
2 MSG2
2 MSG3
3 MSG1
3 MSG2
3 MSG3
4 MSG1
MSG_DET Table
ID
1
2
3
4
You can easliy create a view or table at backend using sql and the access then using Crystal
Cheers
Rahul
Edited by rahulwalawalkar - 26 Oct 2007 at 2:26am
IP Logged
