Grouping records in a single column

I have the table with the records below

Name                              Numbers
francis                             1234
francis                              2345
francis                              6789
francis                              1256
francis                              4567


I need to convert the table to this form using sql. Any help will be appreciated

Name                             Numbers
Francis                            1234/ 2345/6789/1256/4567
olongusAsked:
Who is Participating?
 
ThomasianConnect With a Mentor Commented:
DECLARE @t TABLE (Name varchar(100), Numbers int)
INSERT @t 
SELECT 'francis', 1234
UNION ALL SELECT 'francis', 2344
UNION ALL SELECT 'francis', 1634
UNION ALL SELECT 'francis', 8824
UNION ALL SELECT 'james', 7542
UNION ALL SELECT 'james', 1429

SELECT
	Name
	,STUFF(
		(SELECT '/' + CAST(Numbers as varchar)
		FROM @t
		WHERE T.Name=Name
		FOR XML PATH (''))
    ,1,1,'') AS NameValues
FROM @t T
GROUP BY Name 

Open in new window

0
 
PortletPaulfreelancerCommented:
select distinct
  Name
, stuff((
           select ' /' + convert(varchar,Numbers)
           from table1
           where Table1.Name = t.name
           order by Numbers
           FOR XML path('')
          ), 1, 1, '') as Numbers
from table1 as t
;

Open in new window

see http://sqlfiddle.com/#!3/1b650/1
0
 
olongusAuthor Commented:
The anwers you've provided are excellent answers but I have to apply this to a large data set.

Name                              Numbers
francis                             1234
francis                              2345
francis                              6789
francis                              1256
francis                              4567
James                               9090
James                               4321
James                               0089
Elsa                                   3345
Elsa                                   4567
Tom                                  1433
Tom                                   3244


And the expected result should be

Name                             Numbers
Francis                            1234/ 2345/6789/1256/4567
James                              9090/4321/0089
Elsa                                  3345/4567
Tom                                 1433/ 3244
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
ThomasianCommented:
Have you tried our suggestion? It should work on the sample data you provided.
0
 
olongusAuthor Commented:
Thomasian, I justed tried it on the data and it worked perfectly. Thanks.
0
 
olongusAuthor Commented:
Query worked out perfectly.
0
 
PortletPaulfreelancerCommented:
both queries
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.