Solved

Select with group by AND coalesce??

Posted on 2013-01-21
2
385 Views
Last Modified: 2013-01-21
I have a table with the following data

username          Client
lrbrister            Franklin Insurance
lrbrister            Grant Insurance
gmello              Zuereg Insurance
lrbrister            Harrison Insurance
gmello              Yammo Insurance



I'm trying to do a select so I get this. (There will be upwards of 50 usernames)

username          Client
lrbrister            Franklin Insurance|Grant Insurance|Harrison Insurance
gmello              Yammo Insurance|Zuereg Insurance
0
Comment
Question by:lrbrister
2 Comments
 
LVL 4

Accepted Solution

by:
krtyknmsql earned 500 total points
ID: 38801049
You need to query something like below.
SELECT 
	UserName,
	STUFF((SELECT '|' + Client FROM CSV AS B
	 WHERE A.UserName = B.UserName
	 FOR XML PATH ('')),1,1,'') Clients
FROM CSV AS A
GROUP BY UserName

Open in new window

0
 

Author Closing Comment

by:lrbrister
ID: 38801093
Thanks.
This is my final solution


; WITH cte (AE,Client) AS
(
      SELECT CVT.[Account Executive] AE,
                  C.[Client Name] Client
      FROM dbo.ClientVisitTracking CVT
      LEFT JOIN Client C ON CVT.[Client ID] = C.[Client ID]
      GROUP BY cvt.[Account Executive], C.[Client Name]

)
SELECT AE,
      STUFF((SELECT '|' + Client FROM cte AS B
       WHERE A.AE = B.AE
       FOR XML PATH ('')),1,1,'') Clients
FROM cte AS A
GROUP BY AE
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
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 SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

760 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

21 Experts available now in Live!

Get 1:1 Help Now