Distinct - collect values in new column

Posted on 2010-01-04
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

    Check out the accepted solution to ttp://
    Cheers, Andrew
    LVL 26

    Accepted Solution

    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

    LVL 41

    Assisted Solution

    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


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Suggested Solutions

    Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
    In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

    737 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

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now