Offenseno	Uniquekey	Narrative	OffenseReportUniqueFKey
DCSO99OFF181781	3QXH4V5lP9U88ZCrAJysdl	Additional Abuse types data in this case are:  Abusive Language	3tNHjG7Nz1bOieBCXBVyjv
DCSO99OFF212830	1X0nu5A|5CxQ4{5CLhe7Ra	Additional Abuse types data in this case are:  Abusive Language	1VttHK2kP119spENWOY2XZ
DCSO99OFF214928	2{KuTcQgj3rh4u|cO|pWUq	Additional Abuse types data in this case are:  Abusive Language	0HhCEmJ1LCnxcdCKonxILl
DCSO99OFF224676	0|iTLY0efBWv4sHH1U0qkF	Additional Abuse types data in this case are:  Abusive Language	1T9jMQCenBGuoXLnuJJ6Z{
DCSO99OFF233822	0Q5bfi6m92xeW7iotjIS73	Additional Abuse types data in this case are:  Sexual Abuse	1ucqh6UWj8PRAqygX0WibT
DCSO99OFF236865	3sBAouGOb73wgNLb3REGtD	Additional Abuse types data in this case are:  Abusive Language	2Y5qarZAvFgxQxjd0kuFHc
DCSO99OFF240914	05Cnr|TNDC19pzsveqmtkG	Additional Abuse types data in this case are:  Abusive Language	17o5BbIg9FkP2{v3g2G991
DCSO99OFF241112	3pwtP872nFNxq|KBXhEacH	Additional Abuse types data in this case are:  Threats	3|H4a3mk164xpzALMqLe{4
DCSO99OFF241112	0rqU5EBtX2LwHClda3DVJb	Additional Abuse types data in this case are:  Abusive Language	3|H4a3mk164xpzALMqLe{4
DCSO99OFF241112	15Rog{4x11Ih4rmm2hlW5M	Additional Abuse types data in this case are:  Other	3|H4a3mk164xpzALMqLe{4
DCSO99OFF244819	3T23TX4sLFQPZuggIsIhaa	Additional Abuse types data in this case are:  Abusive Language	3X4LeR00T2hQyTC|Rtu5H4

SELECT 
	OffenseNo,
	STUFF(Narrative,1,CHARINDEX(':',Narrative),'') as Offense
FROM
	YourTable

SELECT
	a.Offenseno,
	'Additional Abuse types data in this case are:  '+
		STUFF(b.Narratives,1,2,'') AS Narratives
FROM
	(SELECT DISTINCT Offenseno FROM YourTable ) AS a
	CROSS APPLY
	(
		SELECT
			', ' AS [text()],
			STUFF(b.Narrative,1,CHARINDEX(':',b.Narrative),'') AS [text()]
		FROM
			YourTable b
		WHERE 
			b.Offenseno = a.Offenseno
		FOR XML PATH('')
	) b(Narratives);

quote:

---

Originally posted by WJHamel

Sunita,

The first example just chops off everything before the ":" in each row and does not concatenate any abuse types from rows with the same offenseno. The second example produces a smaller resultset than the original table contains but it contains rows with only one abuse type as well as a couple of rows with a concatenated set of abuse types in one row.

---