create single string from multiple records ms sql 2005

Posted on 2011-04-29
Medium Priority
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

Ephraim Wangoya earned 2000 total points
ID: 35493904

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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

750 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