?
Solved

Take multiple records and contain to one

Posted on 2014-04-19
8
Medium Priority
?
110 Views
Last Modified: 2014-04-20
Please see my attached example

I have an output that requires multiple records to be displayed for several activities.  However, I was wondering if I can list all my activities in one record as my attached example shows.

Is there a way to do this in SQL without having multiple records in first example?
Sample.xlsx
0
Comment
Question by:al4629740
[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
  • 4
  • 4
8 Comments
 
LVL 27

Expert Comment

by:skullnobrains
ID: 40011028
SELECT STUFF(
             (SELECT ',' + Activity
              FROM Table_Name
              FOR XML PATH (''))
             , 1, 1, '')

you'll need to add require group by clauses and edit the table name.

note that this is specifically complicated in sql server as opposed for example to mysql where you can do this using group_concat in a simple select expression
0
 

Author Comment

by:al4629740
ID: 40011283
If my table is called table1,  can you write an example using the fields in the attached document?
0
 
LVL 27

Accepted Solution

by:
skullnobrains earned 2000 total points
ID: 40011324
try this, and bare with me by trying to debug minor typos : i do not have an ms sql server around and hardly ever used one for years.

assuming you have several agencies, i added a where clause in the subquery.
this requires to rename the table in the subquery, which is why it is a little more complex than the example

i used max(FacilityName) and not facilityName because sql server returns an error if you don't use an aggregate with a group by

SELECT Agency,max(FacilityName),
        STUFF((    SELECT ',' + Activity AS [text()]
                          FROM table1 as sub
                         WHERE table1.Agency = sub.Agency
                         FOR XML PATH('')
                     ), 1, 1, '' ) AS [Activities]
FROM  table1
group by Agency
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:al4629740
ID: 40011744
Is it possible to select distinct activity in the code you had above?
0
 
LVL 27

Expert Comment

by:skullnobrains
ID: 40011760
i think both distinct or add it to the group by should work but i'm unsure. please post results if you give it a try
0
 

Author Comment

by:al4629740
ID: 40011783
Code used

SELECT [Committee Name],max(FacilityName),
        STUFF((    SELECT ',' + Activity AS [text()]
                          FROM frmUnregisteredEvent as sub
                         WHERE frmUnregisteredEvent.[Committee Name] = sub.[Committee Name]
                         FOR XML PATH('')
                     ), 1, 1, '' ) AS [Activities]
FROM  frmUnregisteredEvent
group by [Committee Name]



attached output
Book1.xlsx
0
 

Author Comment

by:al4629740
ID: 40011784
Notice column 3 has some duplicate activities.
0
 
LVL 27

Expert Comment

by:skullnobrains
ID: 40012059
i think i misunderstood previously : what you want is to prevent having things like "a,b,b,c" in that column.

you can use "select distinct ',' + Activity ... FOR XML PATH" in the inner query. afaik, it should provide the desired results.
0

Featured Post

Technology Partners: 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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

801 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