Solved

Grouping records in a single column

Posted on 2013-06-08
7
282 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 22

Expert Comment

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

Author Comment

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

Author Closing Comment

by:olongus
Comment Utility
Query worked out perfectly.
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
both queries
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Problem with SqlConnection 5 109
windbg taking it's time 32 49
Group by and order by clause 28 35
SQL Server 2012 Row Selection 2 29
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 …
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

771 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

11 Experts available now in Live!

Get 1:1 Help Now