All,
I have an attribute that can have multiple values per customer. How do I output this with Output Set Builder.
For example
“Country Served” could be:
USA,
Canada,
England,
Japan,
Australia,
…
The Total List of possible “Countries served” is growing all the time and will continue growing in T_Keyword_Values at random intervals.
A particular constituent might serve 6 to 8 countries, and other none, and yet other just one.
For Output Set Builder I want to get all of the values of “country served” consolidated into a single data element per customer.
Customer_No Country_Served Other_Elements…
1 USA
2 USA, England
3 England, Japan, Africa
4
5 England
I do not want to re-write my approach each time a country served is added to t_keyword_values. The maximum number of countries per constituent will stay reasonable less than 1000 char.
Has anyone solved this problem in the context of Output Set Builder. I think someone may have discussed this during the Nashville Conference Prelude.
--Tom Brown
IT Project Manager
Brooklyn Academy of Music
www.bam.org
tbrown@bam.org
I’ve found this query on the internet that does some interesting things for a single customer. However, this does not seem to get me quite where I want to be.
declare @ValueList varchar(1000)
select @ValueList = coalesce(@ValueList + ', ', '') + key_value
from vxs_cust_keyword
where keyword_no = 424 and customer_no = 1251370
select 'Results = ' + @ValueList
I suspect that if I could get this into a correlated sub query that would help. Or maybe a function.
Tom Brown