Solved

Grouping records in a single column

Posted on 2013-06-08
7
287 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 49

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
How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

 
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 49

Expert Comment

by:PortletPaul
ID: 39233671
both queries
0

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

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 …
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

688 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