Adding multiple records to one line

Question

Post reply

Adding multiple records to one line

marty.seed

SSCrazy

Points: 2661
More actions
May 20, 2010 at 9:35 am

#232679

Hello All,
I have a table that is such
Name College
Smith Auburn
Smith AIU
Smith Emory
Davis UGA
Davis Emory
Franklin Harvard
Franklin UGA
Saveur Taft
I need to output the results as
Name: Smith / College(s): Auburn, AIU, Emory
Name: Davis / College(s): UGA, Emory
Name: Franklin / College(s): Harvard, UGA
Name: Saveur / College(s): Taft
I need to know how to append one to many records to one field.
Any thoughts or advice is greatly appreciated!
Marty

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

kramaswamy SSCoach Points: 18181 More actions · Answer 1

take a look at the Tally tables. That should solve your problem. http://www.sqlservercentral.com/articles/T-SQL/62867/

Lowell SSC Guru Points: 323518 More actions · Answer 2

i think you'll need a FOR XML to concat the colleges;

here's an example, i would have adapted it to your data if you included the CREATE TABLE and INSERT INTO statements:

--results

Resource_Id Skills

101 oracle,sqlserver,vb.net

102 excel,java,sqlserver

103 java,oracle,vb.net

the example:

declare @skills table (Resource_Id int, Skill_Id varchar(20))

insert into @skills

select 101, 'sqlserver' union all

select 101, 'vb.net' union all

select 101, 'oracle' union all

select 102, 'sqlserver' union all

select 102, 'java' union all

select 102, 'excel' union all

select 103, 'vb.net' union all

select 103, 'java' union all

select 103, 'oracle'

---

select * from @skills s1

--- Concatenated Format

set statistics time on;

SELECT Resource_Id,stuff(( SELECT ',' + Skill_Id

FROM @skills s2

WHERE s2.Resource_Id= s1.resource_ID --- must match GROUP BY below

ORDER BY Skill_Id

FOR XML PATH('')

),1,1,'') as [Skills]

FROM @skills s1

GROUP BY s1.Resource_Id --- without GROUP BY multiple rows are returned

ORDER BY s1.Resource_Id

set statistics time off;

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

marty.seed SSCrazy Points: 2661 More actions · Answer 3

Hey Lowell,

That worked great, I have never used stuff or an XML before, gives me something to learn! Thanks!

Lowell SSC Guru Points: 323518 More actions · Answer 4

great marty! glad it's working for you!

that FOR XML is great for concatenating, and the Tally Split is perfect for the opposite...getting a delimited list into rows.

Those two things end up bein a pretty common requirement in real life, good code to put in your toolbox for later.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!