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.

---

DECLARE @tab TABLE(Offenseno	varchar(100), Uniquekey	varchar(100), Narrative	varchar(300), OffenseReportUniqueFKey varchar(100))
INSERT INTO @tab 
VALUES('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')

;With cte 
as (SELECT t.Offenseno, STUFF((SELECT ',' + STUFF(Narrative,1,CHARINDEX(':',Narrative),'') FROM @Tab s WHERE s.Offenseno = t.Offenseno FOR XML PATH('')),1,1,'') AS CSV
	FROM @Tab AS t GROUP BY t.Offenseno)
SELECT c.Offenseno, uniquekey, 'Additional Abuse types data in this case are:' + c.csv as narrative, OffenseReportUniqueFKey
FROM cte c 
JOIN 
(SELECT *, row_number() over(PARTITION by offenseno order by narrative) as rn FROM @tab) t 
ON t.Offenseno = c.Offenseno and rn = 1