<div>
 

<pre><code class="code-10 nolinks" id="code-8168118">select a.probcode,		
		case when dbo.GetValueByIndexfcn(a.probcode , '-', 0) is null then '' else dbo.GetValueByIndexfcn(a.probcode , '-', 0) end as Column0, 
		case when dbo.GetValueByIndexfcn(a.probcode , '-', 1) is null then '' else dbo.GetValueByIndexfcn(a.probcode , '-', 1) end as Column1, 
		case when dbo.GetValueByIndexfcn(a.probcode , '-', 2) is null then '' else dbo.GetValueByIndexfcn(a.probcode , '-', 2) end as Column2, 
		case when dbo.GetValueByIndexfcn(a.probcode , '-', 3) is null then '' else dbo.GetValueByIndexfcn(a.probcode , '-', 3) end as Column3, 
		case when dbo.GetValueByIndexfcn(a.probcode , '-', 4) is null then '' else dbo.GetValueByIndexfcn(a.probcode , '-', 4) end as Column4, 
		case when dbo.GetValueByIndexfcn(a.probcode , '-', 5) is null then '' else dbo.GetValueByIndexfcn(a.probcode , '-', 5) end as Column5, 
		COUNT(a.probcode) as count_of_code
 
 from opencall a group by a.probcode


--Funtion need to return data from this table

SELECT [flags]
 ,[levelx]
 ,[parentcode]
 ,[descx]
 ,[code]
 ,[dd]
 FROM [HornbillLive].[dbo].[probcode]
</code></pre>
</div>

<div>
Have you tried a simple JOIN of opencall and probcode tables?
</div>

<div>
Yeah here is my attempt but the problem is I need each column translating from its ID to the one relating in the table 
 
Unless I am missing something here or over complexing it and guilty as charged with that 

<pre><code class="code-10 nolinks" id="code-8168265">select g.level1,g.level0,f.descx as level2,g.Column0,g.Column1,g.Column2,g.Column3,g.Column4,g.Column5,g.probcode,g.count_of_code from probcode f
 join 
 (select e.descx as level1,d.descx as level0,e.Column0,e.Column1,e.Column2,e.Column3,e.Column4,e.Column5,d.code,e.probcode,e.count_of_code from probcode d
 join 
 (select c.descx,b.Column0,b.Column1,b.Column2,b.Column3,b.Column4,b.Column5,c.code,b.probcode,b.count_of_code from probcode c
 join
 (select a.probcode,		
		case when dbo.GetValueByIndexfcn(a.probcode , '-', 0) is null then '' else dbo.GetValueByIndexfcn(a.probcode , '-', 0) end as Column0, 
		case when dbo.GetValueByIndexfcn(a.probcode , '-', 1) is null then '' else dbo.GetValueByIndexfcn(a.probcode , '-', 1) end as Column1, 
		case when dbo.GetValueByIndexfcn(a.probcode , '-', 2) is null then '' else dbo.GetValueByIndexfcn(a.probcode , '-', 2) end as Column2, 
		case when dbo.GetValueByIndexfcn(a.probcode , '-', 3) is null then '' else dbo.GetValueByIndexfcn(a.probcode , '-', 3) end as Column3, 
		case when dbo.GetValueByIndexfcn(a.probcode , '-', 4) is null then '' else dbo.GetValueByIndexfcn(a.probcode , '-', 4) end as Column4, 
		case when dbo.GetValueByIndexfcn(a.probcode , '-', 5) is null then '' else dbo.GetValueByIndexfcn(a.probcode , '-', 5) end as Column5, 
		COUNT(a.probcode) as count_of_code
 
 from opencall a group by a.probcode)b
 
 on b.Column0 = c.code)e 
 on e.Column1 = d.code)g 
 on g.Column2 = f.code
 
 order by 11 desc
</code></pre>
</div>

<div>
I guess I didn't understand what it is you are trying to do with the data. &nbsp;Thought the functions you were showing where pseudo code for what you wanted created ... sounds like that is a different function and now you need to join on columns 0-N against the other table to get descx. &nbsp;I will take a look and see. 
 
One thought coming to mind now is using a cross join to numbers 0-5 and get this data as rows. &nbsp;Then joining to the other table to get the other columns. &nbsp;Then you can PIVOT to get final results. &nbsp;But will look at function approach as you may just need a simple table-valued function that you can use in CROSS APPLY to your original data multiple times. &nbsp;If you truly need columns from other table for each columnN in the original query then will be about same as joining multiple times to the probcode table itself.
</div>

<div>
Great this is just what I needed 
 
Many thanks
</div>

<div>
<div class="content wysiwyg-content">
Guys 
 
I have no experience in creating functions but believe that to solve an issue I have with some sql code I am writing i think this is the way forward 
 
I have some sql code attached which returns multiple columns that gets it information from a single column it splits up this column but I need to resolve the separate columns and the information is contained in a separate table. 
 
I believe to solve this a function that uses the code field from the HornbillLive.dbo.probcode table to returdn the value contained in descx in the same table HornbillLive.dbo.probcode. I would be wanting to modify the case statements to return the descx value instead of the id it is returning at the moment 
 
Hopefully I have explained my self
</div>
</div>

Guys

I have no experience in creating functions but believe that to solve an issue I have with some sql code I am writing i think this is the way forward

I have some sql code attached which returns multiple columns that gets it information from a single column it splits up this column but I need to resolve the separate columns and the information is contained in a separate table.

I believe to solve this a function that uses the code field from the HornbillLive.dbo.probcode table to returdn the value contained in descx in the same table HornbillLive.dbo.probcode. I would be wanting to modify the case statements to return the descx value instead of the id it is returning at the moment

Hopefully I have explained my self

Create function in sql 2008

Microsoft SQL Server 2008 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. Major improvements include the  Always On technologies and support for unstructured data types.