Function

Question

Function

monique 88355

SSC Journeyman

Points: 86
More actions
October 13, 2014 at 3:25 am

#297329

Hi
Please assist with a SQL Function.
I have the following data table:
DataTable ([Lookup]) as
(
select '1 2' [Lookup] union all
select '2 3' [Lookup] union all
select '3 1' [Lookup]
)
Then I have the following Lookup Table:
LookupTable ([ID], [Descr]) as
(
select 1 [ID], 'Red' [Descr] union all
select 2 [ID], 'Green' [Descr] union all
select 3 [ID], 'Blue' [Descr]
)
I want to create a SQL Function that will give me the following results as I currently have in the Function column. Please run my query below to see results. I do not want to use the Case Statement anymore. I want to replace this with a SQL Function.
with LookupTable ([ID], [Descr]) as
(
select 1 [ID], 'Red' [Descr] union all
select 2 [ID], 'Green' [Descr] union all
select 3 [ID], 'Blue' [Descr]
),
DataTable ([Lookup]) as
(
select '1 2' [Lookup] union all
select '2 3' [Lookup] union all
select '3 1' [Lookup]
)
select
d.[Lookup] [Lookup],
case when d.[Lookup] = '1 2'
then 'Red, Green'
when d.[Lookup] = '2 3'
then 'Green Blue'
when d.[Lookup] = '3 1'
then 'Blue Red'
else NULL end [Function]
from DataTable d
Please assist.
Thank you,
Monique

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

monique 88355 SSC Journeyman Points: 86 More actions · Answer 1

I just want to add that I want to create the function as the data in the DataTable can be loads of combinations, so using a Case Statement does not make sense.

Phil Parkin SSC Guru Points: 246993 More actions · Answer 2

When you call this function, will you always pass exactly 2 arguments (eg 1 2), or do you want to pass a parameter list with a variable list of params?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

monique 88355 SSC Journeyman Points: 86 More actions · Answer 3

Hi

No, the amount will always differ.

Example 1 or 1 2 or 1 1 1 2

monique 88355 SSC Journeyman Points: 86 More actions · Answer 4

So, I want it to work like this

FunctionName (1) = Red

FunctionName (1 2) = Red Green

FunctionName (1 1 1 2) = Red Red Red Green

Phil Parkin SSC Guru Points: 246993 More actions · Answer 5

Here is a solution which relies on Jeff Moden's infamous '8k Splitter'[/url].

It assumes that the DelimitedSplit8K function has been created in the database you are going to call it from.

if object_Id('tempdb..#colour', 'U') is not null

drop table #colour

create table #colour

(

Id int primary key,

Descr varchar(50)

)

insert #colour

(Id, Descr)

values

(1, 'Red'),

(2, 'Green'),

(3, 'Blue')

--select * from #colour c

declare @LookupVal varchar(1000) = '1 3 3 3 2 3'

declare @RetVal varchar(max) = ''

select

@RetVal = @RetVal + c.Descr + ' '

from

dbo.DelimitedSplit8K(@LookupVal, ' ') split

join #colour c on c.Id = split.item

order by

split.ItemNumber

select

@RetVal

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

monique 88355 SSC Journeyman Points: 86 More actions · Answer 6

Hi

Thank you so much! You are awesome!

Can I just ask one more thing?

I've added ', ' to my query [@RetVal = @RetVal + c.Descr + ', '] because I want the data to be separated by a comma. I want it to look like this:

Function (1 2 3) = [Red, Green, Blue]

But it comes out looking like this (it adds an extra comma to the end):

Function (1 2 3) = [Red, Green, Blue,]

I don't want a comma to be added to the end of the row. How can this exception be put into the function so that I do not have to remove the comma in my SQL query?

Please assist this last time 🙂

Jeff Moden SSC Guru Points: 1004435 More actions · Answer 7

monique 88355 (10/13/2014)
Hi
Thank you so much! You are awesome!
Can I just ask one more thing?
I've added ', ' to my query [@RetVal = @RetVal + c.Descr + ', '] because I want the data to be separated by a comma. I want it to look like this:
Function (1 2 3) = [Red, Green, Blue]
But it comes out looking like this (it adds an extra comma to the end):
Function (1 2 3) = [Red, Green, Blue,]
I don't want a comma to be added to the end of the row. How can this exception be put into the function so that I do not have to remove the comma in my SQL query?
Please assist this last time 🙂

I recommend that you post the whole function so that nothing gets broken in the process of helping you figure this out.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Phil Parkin SSC Guru Points: 246993 More actions · Answer 8

Instead of SELECT @RetVal in my above example,

select left(@RetVal,len(@Retval)-1)

Returns everything apart from the rightmost character. This may be what you need ...

--Edit: fixed inconsistent formatting

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

monique 88355 SSC Journeyman Points: 86 More actions · Answer 9

monique 88355

SSC Journeyman

Points: 86

October 15, 2014 at 10:58 pm

#1752722

Thanx for you assistance.

Works perfectly.