Distinct - collect values in new column

Posted on 2010-01-04
Medium Priority
Last Modified: 2012-05-08
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?
Question by:johnkainn
LVL 28

Assisted Solution

TextReport earned 400 total points
ID: 26170513
Check out the accepted solution to ttp://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_20915895.html
Cheers, Andrew
LVL 26

Accepted Solution

tigin44 earned 800 total points
ID: 26171051
try this
select  cId,  MIN(t1text) AS  t1text
	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

LVL 41

Assisted Solution

ralmada earned 800 total points
ID: 26172907
This can be simplified like this:

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

Open in new window


Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are some very powerful Dynamic Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a di…
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Screencast - Getting to Know the Pipeline

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question