February 23, 2004 at 2:05 pm
I have a table that contains transaction information. This table contains a unique id as it's primary key and it has a char(50) field that is used for hold_reasons. Each of these 50 characters represent an on/off status. 1 being on 0 being off.
A transaction can have more than one of the 50 characters set to 1. Which means a transaction can have more than 1 hold reason.
I then have a table with the 50 hold reasons. This table contains a unique id of 1 to 50 with a description. If the transaction record has the 4 character set to 1 then in the hold reason table, id 4 is the description I want to use.
I have to concatenate all the hold reasons together for a particular transaction.
So if a transaction has hold reason 1 and hold reason 3, their hold_reasons would concatenate " 1 description1 , 3 description3"
I need a way to this in SQL even whether it be in a stored procedure, temp tables, what ever.
If you could help I would appreciate it.
I am sure I didn't supply enough info. Let me know if you need to know more.
Thanks.
February 23, 2004 at 3:19 pm
First Create This Function:
CREATE FUNCTION dbo.AllReasons(@Reasons char(50))
RETURNS VArchar(8000)
AS
BEGIN
DECLARE @Result varchar(8000)
SELECT @Result = ISNull(@Result +', ','') + R.Description
FROM
(SELECT Number as n
FROM dbo.Numbers Where Number <=50 ) Nbr
INNER JOIN ReasonsTable R On R.ReasonID = Nbr.n
Where Substring(@Reasons,n,1) <> '0'
Return @Result
END
The Function Assumes you have a "Numbers" Table otherwise you can use
SELECT Number as n FROM master..spt_values Where type ='P' and Number between 1 and 50
Then run something like:
select TranID, dbo.AllReasons(Reasons)
FROM Transac
* Noel
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply