What I needed to happen
There is likely many other more efficient ways to accomplish this, but this is the way I figured out how to do it in my particular situation. I needed to be able to combine column values in a table into a single column to be able to do a filter or search. I'll show you what I mean.
First Table
| RecordId | Name |
| 1001 | Sandy |
| 1002 | Tom |
| 1003 | Jim |
Second Table
This table stores multiple records for each person.
| RecordId | FieldtoConcat |
| 1001 | 1 |
| 1001 | 2 |
| 1002 | 1 |
| 1002 | 3 |
| 1003 | 1 |
| 1003 | 2 |
| 1003 | 3 |
I needed to have this data set returned so I could then filter the list by search for each "FieldtoConcat" in the list.
| RecordId | Name | ConcatedFields |
| 1001 | Sandy | ^1^^2^ |
| 1002 | Tom | ^1^^3^ |
| 1003 | Jim | ^1^^2^^3^ |
The first question you may ask is what's up with the "^". Its a simple reason. If there were any records with a value of, say, "10" when I would search for just "1" then I would get all the records that had "1" in them. So my solution was to search for "^1^" which would ensure that I would only get the desired records.
How to do it
To pull this off you need to create a function. Here it is:
CREATE FUNCTION [dbo].[ConcatField](@RecordId int)
RETURNS VARCHAR(1000)
AS
BEGIN
DECLARE @ReturnValue VARCHAR(1000)
SET @ReturnValue = ''
SELECT @ReturnValue = CASE @ReturnValue
WHEN '' THEN '^' + CAST(FieldtoConcat AS VARCHAR(3)) + '^'
ELSE @ReturnValue + '^' + CAST(FieldtoConcat AS VARCHAR(3)) + '^'
END
FROM TableWithTheFieldtoConcat
WHERE Id = @RecordId
ORDER BY FieldtoConcat
RETURN @ReturnValue
END
It takes the RecordId as a parameter, combines all the numbers into @ReturnValue and returns the values as desired.
After that it gets easy. I'll just show you the SQL because it's pretty self explanatory.
SELECT RecordId, Name, CAST(ConcatField(RecordId) AS VARCHAR(1000)) AS ConcatedFields
FROM FirstTable
You can insert a "WHERE ConcatedFields LIKE %^1^%" and it will only pull the records with "1". That's all there is to it. Anybody have a better way? Please comment.
Technorati Profile