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

If the fields are NULL:

	Select BranchNo, BranchCode, Address =
		ClientName + 
		Case Addrs1 when null then '' else ', ' + Addrs1 end +
		Case Addrs2 when null then '' else ', ' + Addrs2 end +
		Case District when null then '' else ', ' + District end +
		Case Town when null then '' else ', ' + Town end +
		Case County when null then '' else ', ' + County end +
		Case Postcode when null then '' else ', ' + Postcode +
		Case Telephone when null then '' else ', ' + Telephone
	From tblClient where <YOUR_WHERE_CLAUSE>...

If the fields are '' then:

	Select BranchNo, BranchCode, Address =
		ClientName + 
		Case Addrs1 when '' then '' else ', ' + Addrs1 end +
		Case Addrs2 when '' then '' else ', ' + Addrs2 end +
		Case District when '' then '' else ', ' + District end +
		Case Town when '' then '' else ', ' + Town end +
		Case County when '' then '' else ', ' + County end +
		Case Postcode when '' then '' else ', ' + Postcode +
		Case Telephone when '' then '' else ', ' + Telephone
	From tblClient where <YOUR_WHERE_CLAUSE>...

Robert Djabarov
SQL Server & UDB
Sr. SQL Server Administrator
Phone: (210)  913-3148
Pager: (210) 753-3148
9800 Fredericksburg Rd. San Antonio, TX  78288
www.usaa.com

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of paul.hartland at fsmail.net
Sent: Monday, February 16, 2004 3:03 AM
To: SQL Group
Cc: accessd; dba-vb
Subject: [dba-SQLServer] Concatenating Fields In SQL Stored Procedure Or SQLView

To all,
 
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.
 
Paul Hartland
Freeserve AnyTime - HALF PRICE for the first 3 months - Save £7.50 a month 
www.freeserve.com/anytime
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com