Given:
subject, To, Group
----------------------
subject1, name1, -1
subject2, name1, -1
subject2, name2, -1
subject2, 0, group1
Desired format:
Subject, To
-----------------
subject1, name1
subject2, name1, name2, group1
So this is an email table and I'm trying to condense rows like the Desired format: below. Is there an easy way to collapse the To/Group into same subject line?
A little more info: don't need fields where Group = -1 and To = 0
basing on your sample data we can get this using XML path()
declare @t table(subject varchar(10),too varchar(10),groups varchar(10))
insert into @t (subject,too,groups)values
('subject1', 'name1', '-1'),
('subject2', 'name1', '-1'),
('subject2', 'name2', '-1'),
('subject2', '0', 'group1')
;with CTE AS (
select subject,STUFF((select distinct +','+too from @t
where subject = t.subject
for xml path('')),1,1,'')+
STUFF((select distinct +','+groups from @t
where subject = t.subject
for xml path('')),1,1,'')R from @t t
GROUP BY subject )
select subject,
CASE WHEN CHARINDEX(',',R) > 0
THEN REPLACE(SUBSTRING(R, CHARINDEX(',', R) + 1, 20),'-1','')
ELSE replace(R,'-1','') END [To] from CTE
