sql simple function/view to return a string

[azziuhc]

I am kind of new to sql.
I need to write a function or a view table that I will use in a report which returns a string based on sql select

I need to pass (client_id, prim_var, sec_var)
sec_var = can be null
and return a string with all eligible with ',' delimiter

ie: pass 1,A,BC ===> return: descA, descB, descC
ie: pass 2,B,null ===> return: descB
ie: pass 3,A,D ===> return: descA, descD

(table1)
id primary_pointer eligible
-- --------------- --------
1 A BC
2 B (null)
3 A D

(table2)
id primary_pointer coding
-- --------------- ------
1 A codeA
1 B codeB
1 C codeC
1 D codeD
1 E codeE
2 A codeA
2 B codeB
2 C codeC
3 A codeA
3 B codeB
3 C codeC
3 D codeD

(table3)
coding description
------ -----------
codeA descA
codeB descB
codeC descC
codeD descD
codeE descE
....
...
..

Please help
Thank you
d

 

[simian336]

Contantinating a variable number of strings to a single field is not that straight forward.

I have also done it in the past. Here is the best resource I have found. I used the for xml approach.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254

Simi

 

[RyanEK]

Hi,

Use XML Path

--sample input
declare @id int
declare @prim char
declare @sec varchar(5)
select @id = 1, @prim = 'A', @sec = 'BC'

select replace((
 select description as 'data()'
 from (
  select t3.description
  from table2 t2
  join table3 t3 on t2.coding = t3.coding
  where t2.id = @id and t2.primary_pointer = @prim
  union all
  select t3.description
  from table2 t2
  join table3 t3 on t2.coding = t3.coding
  where t2.id = @id and charindex(t2.primary_pointer, @sec) > 0
 ) as t
for xml path('')),' ',', ') as descriptions

Ryan