Concatenate Columns 1

[lyric0n]

Hello,

I would like to create a view of a table in a very specific manor...my table looks like the following

IP | Code
127.0.0.1 | AA
127.0.0.2 | AA
127.0.0.3 | AB

Then for my view, I would like it to look like...

Code | IP
AA | 127.0.0.1, 127.0.0.2
AB | 127.0.0.3

Does anyone have any ideas how to do this? I've tried coalesce but haven't had much luck, thanks, Chris

 

[gmmastros]

Read this. thread183-1159740

Post back if you have any followup questions.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[lyric0n]

That works really well! Thanks!

Just one more question though, if I wanted to send multiple variables to the function, how would I go about doing that? I've tried inputting another variable in the Function, seperated from the other with a comma, then calling it like below...

Create Function GetCompanyNames
(@FilterData Integer, @FilterName varchar(10))...

...
GetCompanyNames(FilterInteger, 'FilterName')

I get the error "Function Argument Count Error", any ideas?

 

[gmmastros]

Can you post the code for the function and also for the query that you are using the function?

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[lyric0n]

Sure...

CREATE Function dbo.fnGetIP (@FilterData Varchar(10), @ColumnName Varchar(10))
Returns VarChar(255)
As
Begin
Declare @Result VarChar(255)

Select @Result = IsNull(@Result + ',', '') + @ColumnName
From dbo.MyTable
Where IPAddr Is Not NULL
And Code = @FilterData

Return @Result
End

Then my view is...

Select
dbo.fnGetIP(dbo.AnotherMyTable.Code, 'DeviceType') As Expr1
From dbo.AnotherMyTable

 

[gmmastros]

I think I see what you are trying to do.

It looks like you are trying to make this function be generic such that you can send the column name that you want concatenated. Unfortunately, I don't think you can do this. Ordinarily, it could be accomlished with dynamic SQL, but since this is a function, that won't work. My best advice to you is to create several different functions for each column that you want concatenated.

Good luck.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[lyric0n]

That is what I was trying to do, oh well, multiple functions it is then,

Thanks much!

 

[gmmastros]

Now that I think about it, you could create 1 function but hardcode the column name, like this...

CREATE Function dbo.fnGetIP (@FilterData Varchar(10), @ColumnName Varchar(10))
Returns VarChar(255)
As
Begin
Declare @Result VarChar(255)

If @ColumnName = [!]'EyeColor'[/!]
  Begin
    Select @Result = IsNull(@Result + ',', '') + [!]EyeColor[/!]
    From   dbo.MyTable
    Where  IPAddr Is Not NULL
           And Code = @FilterData
  End

If @ColumnName = [!]'ShoeSize'[/!]
  Begin
    Select @Result = IsNull(@Result + ',', '') + [!]ShoeSize[/!]
    From   dbo.MyTable
    Where  IPAddr Is Not NULL
           And Code = @FilterData
  End

Return @Result
End

This solution isn't much better than multiple functions, but at least all the code is in a single function (which may make it a little easier to maintain).

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom