title: How to concatenate two or more strings in SQL SERVER 2008 R2?
body: <p>I want to concatenate two or more strings and display them in a single record, line by line in SQL Server 2008 R2.</p>
  
  <p><img src="https://i.stack.imgur.com/oqHeP.png" alt="enter image description here"></p>
  
  <p>What I have is,</p>
  
  <pre><code>shipsite|RatingMonth|Prior|Revised|Comments                    |
  mopha   |Nov 2012   | 1   | 2     |rating.element.manual.asn   |
  mopha   |Nov 2012   | 1   | 2     |rating.element.packaging    |
  mopha   |Nov 2012   | 1   | 2     |rating.element.asn.accuracy |
  mopha   |Nov 2012   | 1   | 2     |rating.element.over.shipment|
  mopha   |Nov 2012   | 1   | 2     |rating.element.cum.imbalance|
  </code></pre>
  
  <p>What I need is,</p>
  
  <pre><code>shipsite|RatingMonth|Prior|Revised|Comments                    |
  mopha   |Nov 2012   | 1   | 2     |rating.element.manual.asn   |
          |           |     |       |rating.element.packaging    |
          |           |     |       |rating.element.asn.accuracy |
          |           |     |       |rating.element.over.shipment|
          |           |     |       |rating.element.cum.imbalance|
  </code></pre>
  
  <p>I can't use any comma(,) or apostrophe (') or no special characters in concatenation operation. So do you have any idea about that.</p>
  
  <p>My Sample Query,</p>
  
  <pre><code>select DISTINCT 
   D30.SPGD30_SHIP_SITE_C As ShipSite, LEFT(DATENAME(MONTH, D30.SPGD30_RATING_MONTH_Y), 3) + ' ' + DATENAME(YEAR, D30.SPGD30_RATING_MONTH_Y) As RatingMonth,
   D30.SPGD30_PRIOR_SCORE_R As Prior, D30.SPGD30_REVISED_SCORE_R As Revised, 
  
  case when SUBSTRING(D30.SPGD30_TRACKED_ADJUSTMENT_X,1,1)='D' then 'Dispute - '+D30.SPGD30_TRACKED_ADJUSTMENT_X --
  when SUBSTRING(D30.SPGD30_TRACKED_ADJUSTMENT_X,1,1)='R' then 'Return Of Points - '+D30.SPGD30_TRACKED_ADJUSTMENT_X --
  when (CHARINDEX('-',D30.SPGD30_TRACKED_ADJUSTMENT_X) &gt; 0 )then 'Score Calculation - '+CONVERT( VARCHAR(8), CAST(D30.SPGD30_TRACKED_ADJUSTMENT_X as DATETIME) , 1)--MM/DD/yy
  
  END AS Adjustments,
  J02.SPGJ02_MSG_CODE_X As Comments,
  --D31.SPGA04_RATING_ELEMENT_D As Comments,
   D30.SPGD30_LAST_TOUCH_Y As LastUpdated,
  D30.SPGD30_LAST_TOUCH_C As LastUpdatedCDSID 
  
  from 
  CSPGD30_TRACKING D30, CSPGD31_TRACKING_RATING_ELEMNT D31,
  CSPGA04_RATING_ELEMENT_MSTR A04 , CSPGJ02_MSG_OBJ J02, CSPGJ03_MSG_TRANSLN J03
  
  Where D30.SPGD30_SHIP_SITE_C = D31.SPGD30_SHIP_SITE_C -- D30 and D31
  and D30.SPGD30_RATING_MONTH_Y = D31.SPGD30_RATING_MONTH_Y --D30 and D31
  and D31.SPGA04_RATING_ELEMENT_D = A04.SPGA04_RATING_ELEMENT_D --D31 and A04
  and J02.SPGJ02_MSG_K = J03.SPGJ02_MSG_K --J02 and J03
  
  and A04.SPGJ02_MSG_K = J02.SPGJ02_MSG_K --A04 and Jo2
  and d30.SPGA02_BUSINESS_TYPE_C = 'prod' -- org
  and d30.SPGA03_REGION_C = 'EU' -- region
  and d30.SPGD30_SHIP_SITE_C = 'ms01a' -- shipsite
  and D30.SPGD30_LAST_TOUCH_Y between '2013-01-01 00:00:00.000'  --rating month
  and '2013-01-30 23:59:59.000' -- rating month
  and d30.SPGD30_LAST_TOUCH_C = 'sadalara' --CDSID
  </code></pre>