Solved

Grouping records in a single column

Posted on 2013-06-08
7
283 Views
Last Modified: 2013-06-09
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
0
Comment
Question by:olongus
  • 3
  • 2
  • 2
7 Comments
 
LVL 22

Accepted Solution

by:
Thomasian earned 500 total points
ID: 39232425
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
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39232426
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
 

Author Comment

by:olongus
ID: 39233004
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 22

Expert Comment

by:Thomasian
ID: 39233051
Have you tried our suggestion? It should work on the sample data you provided.
0
 

Author Comment

by:olongus
ID: 39233393
Thomasian, I justed tried it on the data and it worked perfectly. Thanks.
0
 

Author Closing Comment

by:olongus
ID: 39233394
Query worked out perfectly.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39233671
both queries
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

947 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

23 Experts available now in Live!

Get 1:1 Help Now