?
Solved

Grouping records in a single column

Posted on 2013-06-08
7
Medium Priority
?
288 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 2000 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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

WordPress Tutorial 1: Installation & Setup

WordPress is a very popular option for running your web site and can be used to get your content online quickly for the world to see. This guide will walk you through installing the WordPress server software and the initial setup process.

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.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Suggested Courses

762 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