Distinct - collect values in new column

I have following table
T1Id   CId    T1Text
1         1        One
2         1        Two
3          2       Three
I would like to return distinct values of CId, but also add a new column where the values of all items in T1Text are shown.

Result should be:
CId     NewColumn
1         One, Two
2         Three

How do I do that?
johnkainnAsked:
Who is Participating?
 
tigin44Commented:
try this
select  cId,  MIN(t1text) AS  t1text
FROM (
	select cId, t1text = LEFT(r.text1, LEN(r.text1) -1)
	from (
		select b.*, text1 =(	select c.t1text + ','  as [text()] 
					from yourTable c
					where c.cId = b.cId
					for xml path(''))
		from yourTable b ) as r
	) AS D 	
group by cId

Open in new window

0
 
TextReportCommented:
Check out the accepted solution to ttp://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_20915895.html
Cheers, Andrew
0
 
ralmadaCommented:
This can be simplified like this:

select distinct 
	a.CID,
	newcolumn = stuff((select  ',' + T1Text from yourtable where cID = a.CID for xml path('')), 1, 1, '')
from yourtable a

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.