Multiple Rows into one column

[gtjr921]

I want to combine all the results for the areacodes into one column for each user. like a csv.


I want the count of the top 5 for each particular name. I know how to get that, I don't know how to get it to display in one column
I tried reading a few articles on putting multiple colums in one row and i could not figure out what i am missing!
I tried my code below but the results just return the same 5 of areacodes for all names on each area code row with each callername. like

name AreaCodes
joe blow 123,456,755,312,465,567,555
bill jones 123,456,755,312,465,567,555

I want it to be like
name AreaCodes
joe blow 123,456,755,312,465
bill jones 604,259,643,367,984

Here is my code

DECLARE @Stage TABLE (RowID INT IDENTITY(1, 1), CallerName VARCHAR(256), AreaCode VARCHAR(3), Calls INT, theDate DATETIME,myareacodes int)
declare @MyAreaCodes varchar(50)
INSERT @Stage
(
CallerName,
AreaCode,
Calls,
theDate


)  --This is where the area code data comes from I can get it to display in multiple colums, but -- I want the area codes to be on one line for each name
SELECT CallerName,
SUBSTRING(TargetNum, 2, 3) AS AreaCode,
COUNT(*) AS AreaCodeCount,
DATEADD(day, DATEDIFF(day, 0, GtCalcDate), 0) as myDate
FROM CDRMAIN
WHERE LEN(TargetNum) >= 11
AND TargetNum NOT LIKE '_800%'
AND GtCalcDate >= '2006-11-06'
AND GtCalcDate < '2006-11-07'
GROUP BY CallerName,
SUBSTRING(TargetNum, 2, 3),
DATEADD(day, DATEDIFF(day, 0, GtCalcDate), 0)
ORDER BY CallerName,
COUNT(*) DESC

-- Get Calls
SELECT s.CallerName,
s.AreaCode,
s.Calls,
s.theDate --
FROM @Stage s
INNER JOIN (
SELECT CallerName,
MIN(RowID) mirw,
4 + MIN(RowID) marw
FROM @Stage
GROUP BY CallerName


HAVING   (CallerName = 'name1') OR   (CallerName = 'name2') 
) q ON q.CallerName = s.CallerName AND s.RowID BETWEEN q.mirw AND q.marw
ORDER BY callername,Calls desc
--
set @MyAreaCodes =''
--
SELECT top 5 @MyAreaCodes = @MyAreaCodes + ISNULL(AreaCode,'') + ',' from @Stage
Group by callername,areacode
HAVING   (CallerName = 'name1') OR   (CallerName = 'name2') 

--
SELECT CallerNAme,@MyAreaCodes AS MyAreaCodes from @stage
Group By CallerName

 

[hmckillop]

Have a look at the crosstab queries FAQ ( faq183-5269), they do this sort of stuff.
Select the FAQs link just above the "reply to thread" link


"I'm living so far beyond my income that we may almost be said to be living apart