How can I in sql

[oxenss]

I am trying to get text records out of a database the problem I am having is we have limited the text to being 75 char long so there is multiple lines for each comment_ID and I want to group the multiple lines together.
I have thrown my SQL statement in that i have so far
SELECT CitationComment.Comment_ID, EnglishText.EnglishText_ID, CitationComment.Employee_ID, EnglishText.EnglishText_Text
FROM (CommentText INNER JOIN ((Comment INNER JOIN CitationComment ON Comment.Comment_ID = CitationComment.Comment_ID) INNER JOIN CommentLink ON Comment.Comment_ID = CommentLink.Comment_ID) ON CommentText.CommentText_ID = CommentLink.CommentText_ID) INNER JOIN EnglishText ON CommentText.CommentText_ID = EnglishText.CommentText_ID;

so what i am trying to do is get the citationcomment.comment_ID that has multiple records for englishtext.englishtext to combine
did this make anysince

thank you for the help in advance


The OX

 

[sunaj]

Hi,

You don't write which database you use. If you use SQL server you can do it with scalar user function.
Here is an example with 2 tables (tblTest & tblTestChild).
-------------------------------------------------
create function ConcatTestChild(@tblTestID int)
Returns varchar(1024) as
BEGIN
Declare @s varchar(1024)
Set @s=''
select @s=@s + TC.Value from tblTestChild TC WHERE TC.tblTestID=@tblTestID -- this relies on TC.Value never being NULL, if so use COALESCE
Return @s
END
-------------------------------------------------
SELECT *,dbo.ConCatTestChild(tblTestID) FROM tblTest
-------------------------------------------------

I don't know if other database systems has simlar options. It is not a possiblity with Access. If you use Access, the only solution I can come up with is to do it in the front end.

Sunaj
'The gap between theory and practice is not as wide in theory as it is in practice'