Concatinating Records 2

[robertfah]

I've got a table with Requests in it and another table with RequestHistory info in it. In the RequestHistory table there is a field called Disposition which hold text data. For each Request there could be none or many RequestHistory entries.

Is there a way to pull back all the data in the Request table and all the Disposition data and have it for 1 record for each Request? Something like this:

Data:

Request
RID Request Date
1 Test 1/1/2000
2 Test 2 2/2/2000

RequestHistory
RHID RID Disposition
1 1 Request Opened.
2 1 Request has been discussed
3 1 Request Closed.
4 2 Request Opened.
5 2 Request Closed.

Desired Output
RID Request Disposition
1 Test Request Opened. Request has been discussed. Request Closed.
2 Test 2 Request Opened. Request Closed.


Any idea as to how I can do this with a stored procedure?

 

[markros]

See the exact same topic discussed here today

http://forums.asp.net/t/1442148.aspx

and

http://knowledgebaseworld.blogspot.com/2008/12/string-concatenation-in-sql.html

 

[robertfah]

I'm not too sure those help me out. I'm running SQL2000 so the first link doesn't help me and the 2nd one uses 1 table where I am using two. Can someone help me understand this if it will work? I'm not following the logic with the 2nd link above.

 

[markros]

If you're using SQL Server 2000, then just use Nicola's solution and join with your table, e.g.

select T1.*, dbo.GetInfo(T2.FK) as SummaryGroupInfo from T1 inner Join t2 on T1.Pk = T2.Pk

And create a separate scalar function to get your info for Group ID.

 

[gmmastros]

First, Create this function.

Create Function dbo.GetRequestHistoryDisposition
	(@RID Int)
Returns VarChar(8000)
As
Begin
    Declare @Output VarChar(8000)

    Set @Output = ''

    Select @Output = @Output + ' ' + Disposition
    From   RequestHistory
    Where  RID = @RID
           And Disposition > ''
    Order By RHID

    Return Trim(@Output)
End

Then run this to get your output:

Select RID, Request, dbo.GetRequestHistoryDisposition(RID)
From   Request

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[robertfah]

gmmastros,

Thank you for showing this to me. I had an idea but was a little foggy on the syntax and such. Once again you pull through for me.

Markros,

Thanks for the links to the code. They helped a little but I just couldn't decifer (sp?) the code.

 

[robertfah]

Another related ?......how can I make this function dynamic? I tried the following but I get errors:

ALTER   FUNCTION dbo.ASC_NGSC_FUNC_GetRequestComments 
(@RequestID int, @DBName varchar(20))

RETURNS varchar(8000)

AS

BEGIN

	DECLARE 	@Comments varchar(8000),
				@SQLString varchar(8000),
				@DB varchar(100)

	SET @DB = 'SC_' + @DBName + '.dbo.'

	SET @SQLString = '
	SELECT @Comments = ISNULL(@Comments + '', '', '''') + [Comments]
	FROM ' + @DB + 'Requests csd_r (NOLOCK)
	INNER JOIN ' + @DB + 'RequestHistory pr (NOLOCK) ON (csd_r.RequestID = pr.RequestID)
	WHERE csd_r.RequestID = @RequestID
	AND Comments > '''

	EXECUTE @SQLString

	RETURN @Comments

END

SELECT dbo.ASC_NGSC_FUNC_GetRequestComments(82, 'uasf') gives me this error:

Server: Msg 203, Level 16, State 2, Procedure ASC_NGSC_FUNC_GetRequestComments, Line 38
The name '
SELECT @Comments = ISNULL(@Comments + ', ', '') + [Comments]
FROM SC_uasf.dbo.Requests csd_r (NOLOCK)
INNER JOIN SC_uasf.dbo.RequestHistory pr (NOLOCK) ON (csd_r.RequestID = pr.RequestID)
WHERE csd_r.RequestID = @RequestID
AND Comments > '' is not a valid identifier.


any ideas?

 

[markros]

I don't think you can do this dynamic. Are you 100% sure you need to go dynamic?

For now instead of EXECUTE put PRINT - you will see errors in the string you're creating.

But I don't even know if you can use dynamic SQL from the function.