[dba-VB] [dba-SQLServer] Concatenating Fields In SQL Stored Procedure Or SQL View

> I have a table in SQL Server 7.0 called tblClient with the following
> fields of information:
>             BranchNo
>             BranchCode
>             ClientName
>             Addrs1
>             Addrs2
>             District
>             Town
>             County
>             Postcode
>             Telephone
> 
> I want the following fields ClientName, Addrs1, Addrs2, District, Town,
> County, Postcode to appear as one line separated by a comma like below
> 
> BranchNo           BranchCode       Address
> 9999029            B&Q9999029      ClientName, Addrs1, District, Town,
> Postcode
> 
> As you can se from the example Addrs2 and County are missing, this is
> because these are blank sometimes.  I have tried a couple of ways but
> always end up with the following
> 
> BranchNo           BranchCode       Address
> 9999029            B&Q9999029      ClientName, Addrs1, , District, Town, ,
> Postcode
> 
> As you can see where the missing fields are the comma’s still appear.  Any
> help on this will be gratefully received.

It's a bit clumsy, but you could use:

Select BranchNo,
   BranchCode,
   ClientName + ', ' + Addrs1 + ', ' + 
   Case 
      When  Addrs2 > ' ' then  Addrs2 + ','
      Else ''
   End 
   + District + "," + Town + "," +
   Case 
      When County > ' ' then  County + ','
      Else ''
   End 
   + Postcode

from tblClient


-- 
Stuart McLachlan
Lexacorp Ltd
Application Development,  IT Consultancy
http://www.lexacorp.com.pg

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com