Monday, February 20, 2012

Multiple Rows to One Column Results

I want to display the top 5 count of areacodes for each name
I

want to combine all the results for the areacodes into one column for

each user. like a csv. 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
joe blow 123,456,755,312,465,567,555
bill jones 123,456,755,312,465,567,555

I just want the top 5 for each particular name.
I tried reading a few articles on putting multiple colums in one row and i could not figure out what i am missing!
Thanks

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
--myAreacodes

) --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--,myareacodes
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

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

This should do it

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
--myAreacodes
) --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-17'
GROUP BY CallerName,
SUBSTRING(TargetNum, 2, 3),
DATEADD(day, DATEDIFF(day, 0, GtCalcDate), 0)
ORDER BY CallerName,
COUNT(*) DESC

select CallerName, coalesce(AreaCode1 + ', ' + AreaCode2 + ', ' + AreaCode3 + ', ' + AreaCode4 + ', ' + AreaCode5, AreaCode1 + ', ' + AreaCode2 + ', ' + AreaCode3 + ', ' + AreaCode4, AreaCode1 + ', ' + AreaCode2 + ', ' + AreaCode3, AreaCode1 + ', ' + AreaCode2, AreaCode1)
from (select s0.CallerName, s1.AreaCode AreaCode1, s2.AreaCode AreaCode2, s3.AreaCode AreaCode3, s4.AreaCode AreaCode4, s5.AreaCode AreaCode5
from (select CallerName, min(RowID) as RowID from @.stage group by CallerName) s0
left outer join @.stage s1 on s1.CallerName = s0.CallerName AND s1.RowID = s0.RowID
left outer join @.stage s2 on s2.CallerName = s0.CallerName AND s2.RowID = s0.RowID + 1
left outer join @.stage s3 on s3.CallerName = s0.CallerName AND s3.RowID = s0.RowID + 2
left outer join @.stage s4 on s4.CallerName = s0.CallerName AND s4.RowID = s0.RowID + 3
left outer join @.stage s5 on s5.CallerName = s0.CallerName AND s5.RowID = s0.RowID + 4) Top5Calls

No comments:

Post a Comment