COALESCE help

I have a pulldown menu which has like 4 options

producta productb productc and all

I am trying to retrieve the maximum build number value for these products and display on the gridview as per some other conditions like user selected OS etc

Now clicking on All, I want to display the maximum build number values for productA,ProductB ,ProductC

and I am trying to use coalesce but unable to get my result.

I end up seeing only one value which is the maximum of everything.Instead I want the maximums of A B and C and display them concatenated with commas.

If I do the following with no max funciton, i see all the values but i just want max from each branch.

DECLARE @.buildListvarchar(100)

select @.buildlist=COALESCE(@.buildList+', ','')+convert(varchar(10),build)from results

where branchin('ProductA','Product B','ProductC')

select @.buildList

Please let me know how to do this.

Please post some sample data from the table and expected output..

|||

Package Branch maxBuildNumber

Package1 Product A 2001

Package1 Product B 3004

Package1 Product C 4003

I want it as

Package buildList

Package1 2001,3004,4003

or better yet

Package1 ProductA.2001,ProductB.3004,ProductC.4003

|||

Close...

Declare @.TTable (Packagevarchar(10), Branchvarchar(10), maxBuildNumberint)Insert into @.TSelect'Package1','ProductA', 2001unionallSelect'Package1','ProductB', 3004unionallSelect'Package1','ProductC', 4003DECLARE @.buildListvarchar(100)SELECT @.buildlist=COALESCE(@.buildList +', ','') +convert(varchar(10),branch) +'.' +convert(Varchar, T.maxBuildNumber )FROM @.T TWHERE T.Package ='Package1'Select @.buildList

|||

Hi ,

Thank you for the coalesce help.Now I have a small problem with in that.

I do not want the whole of the branch name to be displayed in my buildlist(name of my branches are pretty long ..so want to display a short name instead)

I have this coalesce in a scalar function where I am returning it as a varchar.

Now before returning it, is it possible to check this buildlist for a pattern and replace it ?

suppose it is being displayed as productA/xy/ABCD.301 ....i want to display it as ABCD.301.

The value coming for Branch from my results table is something like productA/xy/ABCD.

I tried using Contains and replace but do not seem to work on declared variables?

This is what is in my coalesce

SELECT @.buildlist=COALESCE(@.buildList+' ','')+convert(varchar(50),Branch)+'.'+convert(Varchar, v_allbranchinfo.MAXBuildNumber)

FROM v_allbranchinfoWHERE /*some where conditions*/

IF @.buildlistcontains(@.buildlist,"Orcas/pu/DDE")

replace(buildlist,"Orcas/pu/DDE","DDE")

Can you please with this?

|||

After getting the @.Buildlist you can do a replace..

IF CHARINDEX(@.buildlist,'Orcas/pu/DDE') > 0SET @.BuildList =REPLACE(@.buildlist,'Orcas/pu/DDE','DDE' )

|||

You are great!

Thank you very much!

|||

Another problem now with coalese, I am getting the achived result as having all the branch build numbers in one row but on my webpage when I am displaying these results, I use a hyper link which would show some addition info from the same results table such as runid,total etc corresponding to each build.Now when I have the buildlist, I am not sure on how to handle this .

My query now looks like this:

(SELECT T1.*, T1.BuildListAS dataFROM v_BuildListerAS T1INNERJOIN

(SELECT SKU, OS, OSLang, ProductLang, Branch,MAX(Build)AS MaxBuild

FROM dbo.ResultsGROUPBY SKU, OS, OSLang, ProductLang, Branch)AS T2ON T1.SKU= T2.SKUAND

T1.OS= T2.OSAND T1.OSLang= T2.OSLangAND T1.ProductLang= T2.ProductLang)AS T3ON

dbo.Results.ID= T3.IDWHERE(dbo.Results.OSArch='Intel')AND(dbo.Results.OSLang='English - United States')AND(dbo.Results.ProductLang='ENU'))

v_Buildlister is a view on results table and the view doesn't have this runid etc information.

Even if it does, since it is a buildlist and not single build...does not give me proper information.

previously my T1 was results table .

Any idea will be appreciated.