-1

I want to concatenate two or more strings and display them in a single record, line by line in SQL Server 2008 R2.

enter image description here

What I have is,

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|

What I need is,

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|

I can't use any comma(,) or apostrophe (') or no special characters in concatenation operation. So do you have any idea about that.

My Sample Query,

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) > 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
Bart
  • 19,692
  • 7
  • 68
  • 77
Adalarasan_Serangulam
  • 700
  • 4
  • 9
  • 17
  • 1
    possible duplicate of [How to use GROUP BY to concatenate strings in SQL Server?](http://stackoverflow.com/questions/273238/how-to-use-group-by-to-concatenate-strings-in-sql-server) – Joe Feb 22 '13 at 07:03
  • GROUP BY query. Follow the instructions from the duplicate thread. – Joe Feb 22 '13 at 07:04
  • @joe i'm not using the group by here. if i use it shows the shipsite multiple times ,but i want only one time. – Adalarasan_Serangulam Feb 22 '13 at 07:11
  • I think you do not understand GROUP BY, then, as that's the point of GROUP BY. – Joe Feb 22 '13 at 07:14
  • @joe i know the group by , but what i need is after to do the group i want to display the comments one by one for the same record. could you please help me. – Adalarasan_Serangulam Feb 22 '13 at 09:09

1 Answers1

2
SELECT shipsite, RatingMonth, Prior, Revised, Comments = STUFF((
  SELECT CHAR(13) + CHAR(10) + comments
    FROM dbo.YourTable AS x2 
      WHERE x2.shipsite = x.shipsite
      AND x2.RatingMonth = x.RatingMonth
      AND x2.Prior = x.Prior
      AND x2.Revised = x.Revised
    FOR XML PATH, 
    TYPE).value(N'/text().[1]', N'nvarchar(max)'), 1, 2, N'')
FROM dbo.YourTable AS x
GROUP BY shipsite, RatingMonth, Prior, Revised;
Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490