create single string from multiple records ms sql 2005

Posted on 2011-04-29
Last Modified: 2012-05-11
I have a table with records.They are grouped by an id.

say john 1
mike 1
terry 1
frank 2
jerry 2
misty 3
harry 3
molly 3
sam 3

I need to create a table (3 records inthis case)

john maike terry (record 1)
frank jerry (record 2)
misty harry molly sam (record 3)

What query can I use
Question by:robrodp
    1 Comment
    LVL 32

    Accepted Solution


    SELECT id, Stuff((Select  ' ' + name
                      From Table1 T2
                      Where T1.ID = T2.ID
                      For xml Path(''))                    
                      , 1, 1, '') as Names
    From    Table1 T1
    Group By ID

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
    Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
    how to add IIS SMTP to handle application/Scanner relays into office 365.
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

    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

    17 Experts available now in Live!

    Get 1:1 Help Now