title: how to add new line in mysql concat?
body: <p>Here I have attach my query. I want to display address, line by line . </p>
  
  <pre><code>   SELECT 
      tp.acct_name,
      tp.acct_no,
      tp.acct_type,
      tp.sub_type,
      tp.eci_acct_no AS bs_ship_fwd_no,
      tp.ecifwno AS bs_cons_no,
      tp.ssline_number,
      sc.code AS sales_person,
      tp.disabled,
      CONCAT(IF(ca.address1 IS NULL OR ca.address1 = '',
                  ' ',
                  ca.address1),
              ', ',
              IF(ca.city1 IS NULL OR ca.city1 = '',
                  ' ',
                  CONCAT('City:', ca.city1)),
              ' ',
              IF(ca.state IS NULL OR ca.state = '',
                  ' ',
                  CONCAT('State:', ca.state)),
              ' ',
              IF(cy.codedesc IS NULL OR cy.codedesc = '',
                  ' ',
                  CONCAT('Country:', cy.codedesc)),
              ' ',
              IF(ca.zip IS NULL OR ca.zip = '',
                  ' ',
                  CONCAT('Zip:', ca.zip)),
              ' ',
              IF(ca.phone IS NULL OR ca.phone = '',
                  ' ',
                  CONCAT('PH#:', ca.phone))) AS address,
      ca.city1 AS city,
      ca.state,
      cy.codedesc AS country,
      ca.zip,
      ca.contact_name,
      ca.phone,
      ca.fax,
      ca.email1,
      gi.poa,
      gi.fw_fmc_no,
      CONCAT(cr.codedesc, '-', cr.id) AS credit_status,
      ar.credit_limit,
      tp.forward_account
  FROM
      (SELECT 
          acct_name,
              acct_no,
              acct_type,
              sub_type,
              eci_acct_no,
              ecifwno,
              ssline_number,
              disabled,
              forward_account
      FROM
          trading_partner
      WHERE
          (acct_no LIKE '080STU0001%' OR acct_name LIKE '080STU0001%' OR search_acct_name LIKE '080STU0001%') AND (acct_type LIKE '%S%' OR (acct_type LIKE '%V%' AND sub_type = 'Forwarder') OR acct_type LIKE '%O%' OR acct_type = 'C')
      ORDER BY acct_no
      LIMIT 50) AS tp
          JOIN
      cust_address ca ON (tp.acct_no = ca.acct_no AND ca.prime = 'on')
          LEFT JOIN
      genericcode_dup cy ON ca.country = cy.id
          LEFT JOIN
      cust_general_info gi ON tp.acct_no = gi.acct_no
          LEFT JOIN
      genericcode_dup sc ON gi.sales_code = sc.id
          LEFT JOIN
      cust_accounting ar ON tp.acct_no = ar.acct_no
          LEFT JOIN
      genericcode_dup cr ON ar.credit_status = cr.id
  GROUP BY tp.acct_no
  </code></pre>
  
  <p>I need output in following format. Please Help</p>
  
  <pre><code>1771 EAST 9TH AVENUE,
  CITY:AMPA,STATE:FL,COUNTRY:UNITED STATES,
  ZIP:33605  
  </code></pre>
  
  <p>Thanks in advance...</p>