Still not clear what are you after.

You may use table function (you may find in amongst function templates in Query Analyzer).

But I would suggest to consider using a view...

But according to this:

"i want to create a select query within a function that is able to concatenate multi values in each column which is then returned to the call...

Oh, Peplesoft... Well, well.

Heard it somewhere. Probably when I was doing extract for importing data into Peoplesoft system.

What I can suggest is to...

From .. To range from second row covers whole range from 1st row.

So, the task as you describe it looks like MI-IV.

Probably if...

Jeff,

there is no need for a cursor if you use my function:

SELECT code_list_id, dbo.List_CharLong(code_list_text, ',')

FROM CodesTable

It returns same set of data.

So, return to the question:

what kind of query you gonna need to run to find items having code "Charlie" assigned?

So, if code "Bravo" is not there this "obe way" "black box" will put "Charlie" into Code_2 column, and to select items having code "Charlie" you need to search in...

But beware!

select datalength (N' ') = 2

So, what's wrong?

'09100U' >= '091000' - TRUE

'09100U' <= '091499' - TRUE

Line to be returned.

Evan,

so, is it true what you said that code "Bravo" may appear only as Code_2 and "Charlie" only as Code_3 and you not gonn search for "Charlie" in Code_2 column...

So, if you gonna need to remove code "Bravo" from item 1 what's gonna be the result?

NULL in Code2?

Or Code3 will become Code2?

You are mixing RDMS database with Excel spreadsheet.

I understand your desire to keep table in the way suitable for human reading, but it's not a human who will need...

To get this you need to use UDF.

One I've posted some time ago is here:

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=310562&p=4

The way you want it is not any better then it is now.

What if 6th code will appear one day?

It should be:

1 'Alpha'

1 'Bravo'

1 'Charlie'

1 'Delta'

1...

Many OR's is a sign of bad design.

Probably not that bad, but anyway.

Those OR's indicate some business rules hardcoded in query instead of being placed into table(s).

If they would be...