paul.hartland at fsmail.net
paul.hartland at fsmail.net
Mon Feb 16 09:32:51 CST 2004
Thanks Robert..... Message date : Feb 16 2004, 03:25 PM >From : "Djabarov, Robert" To : dba-sqlserver at databaseadvisors.com Copy to : Subject : RE: [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 ... 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 ... 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 _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com Freeserve AnyTime - HALF PRICE for the first 3 months - Save £7.50 a month www.freeserve.com/anytime