Solved

Take multiple records and contain to one

Posted on 2014-04-19
8
109 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 500 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

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

Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

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.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

724 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