Multiple Rows to One Column Results

Question

Multiple Rows to One Column Results

Gail-378561

SSCommitted

Points: 1602
More actions
November 8, 2006 at 11:19 am

#115045

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 all of the areacodes for all names on each area code row with each callername. like
joe blow 123,456,755,312,465,567,555
I just want the top 5 for each particular name.
I tried readin a few articles and i could not figure out what i am missing!
Thanks
DECLARE@Stage TABLE (RowID INT IDENTITY(1, 1), Name VARCHAR(256), AreaCode VARCHAR(3), Calls INT, theDate DATETIME,strValues VarChar(8000))
DECLARE @strValues varchar(8000)
INSERT@Stage
( Name,
AreaCode,
Calls,
theDate,
strvalues
)
SELECTName,
SUBSTRING(TargetNum, 2, 3) AS AreaCode,
COUNT(*) AS AreaCodeCount,
GtCalcDate,[Result]=@strValues
FROMCDRMAIN
WHERELEN(TargetNum) >= 11
AND TargetNum NOT LIKE '_800%'
AND GtCalcDate >= '2006-11-06'
AND GtCalcDate < '2006-11-07'
GROUP BYCallerName,
SUBSTRING(TargetNum, 2, 3),GtCalcDate
ORDER BYName,
COUNT(*) DESC
-- Do the work
SELECTs.Name,
s.AreaCode,
s.Calls,
s.theDate
FROM@Stage s
INNER JOIN(
SELECT[Name],
MIN(RowID) mirw,
4 + MIN(RowID) marw
FROM@Stage
GROUP BYName
HAVING (Name = Name1) OR (Name = 'Name2')
) q ON q.CallerName = s.CallerName AND s.RowID BETWEEN q.mirw AND q.marw
ORDER BYs.RowID
SELECT @strValues = COALESCE(@strValues+',', '') + AreaCode
From @Stage
select distinct Name,thedate, @strValues As Acodes from @Stage
Group by Name,AreaCode,TheDate
HAVING (Name = Name1) OR (Name = 'Name2') [/code]

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323482 More actions · Answer 1

the normal Caveats for this question: you should do this at the presentation layer, whether it is an app or a web page, because it's easier.

here's one way to do it in SQL:

declare @MyAreaCodes varchar(50)

set @MyAreaCodes =''

SELECT top 5 @MyAreaCodes = @MyAreaCodes + ISNULL(AreaCodes,'') + ',' from sometable

print @MyAreaCodes

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Gail-378561 SSCommitted Points: 1602 More actions · Answer 2

i tried this and a few other things, but just can't get the correct syntax

DECLARE@Stage TABLE (RowID INT IDENTITY(1, 1), CallerName VARCHAR(256), AreaCode VARCHAR(3), Calls INT, theDate DATETIME,myareacode int)

declare @MyAreaCode varchar(50)

INSERT@Stage

(

CallerName,

AreaCode,

Calls,

theDate

--myAreacode

)

SELECTCallerName,

SUBSTRING(TargetNum, 2, 3) AS AreaCode,

COUNT(*) AS AreaCodeCount,

DATEADD(day, DATEDIFF(day, 0, GtCalcDate), 0)

FROMCDRMAIN

WHERELEN(TargetNum) >= 11

AND TargetNum NOT LIKE '_800%'

AND GtCalcDate >= '2006-11-06'

AND GtCalcDate < '2006-11-07'

GROUP BYCallerName,

SUBSTRING(TargetNum, 2, 3),

DATEADD(day, DATEDIFF(day, 0, GtCalcDate), 0)

ORDER BYCallerName,

COUNT(*) DESC

-- Do the work

SELECTs.CallerName,

s.AreaCode,

s.Calls,

s.theDate

FROM@Stage s

INNER JOIN(

SELECTCallerName,

MIN(RowID) mirw,

4 + MIN(RowID) marw

FROM@Stage

GROUP BYCallerName

HAVING (CallerName = 'Beth Scharstein') OR

(CallerName = 'name1') OR

(CallerName = 'name2')

) q ON q.CallerName = s.CallerName AND s.RowID BETWEEN q.mirw AND q.marw

ORDER BYs.RowID

set @myAreaCode ='SELECT top 5 @myAreaCode = @myAreaCode + ISNULL(AreaCode,'') + ',' from @stage'

print @myAreaCode

Lowell SSC Guru Points: 323482 More actions · Answer 3

does this work?

change the last statement at the end to the following:

~~set @myAreaCode ='SELECT top 5 @myAreaCode = @myAreaCode + ISNULL(AreaCode,'') + ',' from @stage'~~

set @myAreaCode ='''

SELECT top 5 ISNULL(AreaCode,'') from @Stage --does this return a recordset?

set @MyAreaCodes =''

SELECT top 5 @MyAreaCodes = @MyAreaCodes + ISNULL(AreaCodes,'') + ',' from @Stage

SELECT @MyAreaCodes AS MyAreaCodes

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Gail-378561 SSCommitted Points: 1602 More actions · Answer 4

this almost does it, it returns the same myareacode list for every caller, and it is not the top 5 by count for each name.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 5

Really? Easier in the App? Could I see an example, please? And don't forget to report what the performance is... thanks.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 6

Gail,

Sorry I don't have time to modify this code snippet I borrowed from some of the production code I wrote for work... lemme give you a couple of hints...

"NPA" is the same as "Area Code".
You would replace "CustID" with your CustomerName.
Obviously, you would change other column names and the table name to suit you.
The "cascade variable update" to get "rank" should only be used with a newly populated temp table because a temp table is the only type of table that no one but the current session can possibly use and the clustered index is absolutely clean.
Most folks would have used a function (a minor form of RBAR) to do the CSV portion (Top5NPA column). I did it using a final set based loop, instead. Probably could have used my friend the "Tally" table instead of the loop but that blew the minds of folks at work when I tried to use it for other things.
The whole thing takes only 20 seconds on a poorly indexed usage table... 17 of those seconds are just getting the call counts for each NPA by CustID. It's a bit slow for only 4.34 million rows but it's a third party table that I can't touch the indexes on.
All the rest takes only 3 seconds to return the results for over 39,000 customers.
We're going to hear a lot of guff from some folks about #4 above. Ignore them... I've been using the method with temp tables for a long time and have never had one go bad... not ever.

Here's the snippet for you to modify to suit your needs...

--===== If the working table exists, drop it

IF OBJECT_ID('TempDB..#MyHead') IS NOT NULL

DROP TABLE #MyHead

--===== Recreate the working table

CREATE TABLE #MyHead

(

RowNum INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,

CustID INT,

NPA VARCHAR(20),

CallCount INT,

GroupRank INT,

Top5NPA VARCHAR(20)

)

--===== Declare and preset local variables

DECLARE @PrevCust INT

SET @PrevCust = -2000000000 --No customers way down here

DECLARE @MyCount INT

--===== Populate the working table with data grouped by CustID to count calls per NPA

-- with the call counts in descending order. This takes 17 seconds to resolve over

-- 4.34 MILLION Call Detail Records (CDR's) down to 176,668 records

INSERT INTO #MyHead

(CustID,NPA,CallCount,Top5NPA)

SELECT CustID,

NPA = SUBSTRING(TermNumber,1,3),

CallCount = COUNT(*),

Top5NPA = SUBSTRING(TermNumber,1,3)

FROM Prod_Usage_US_20060225.dbo.Usage --select count(*) from Prod_Usage_US_20060225.dbo.Usage

GROUP BY CustID, SUBSTRING(TermNumber,1,3)

ORDER BY CustID ASC, CallCount DESC, NPA ASC

--===== Do a cascaded variable update to rank each NPA for each CustID

UPDATE #MyHead

SET @MyCount = GroupRank = CASE

WHEN CustID <> @PrevCust

THEN 1

ELSE @MyCount+1

END,

@PrevCust = CustID

FROM #MyHead

--===== We already have the first NPA in the Top5NPA column... go get the next 4 by rank

-- for each custid in a columnar loop (not RBAR, updates whole sets in column 4 times)

SET @MyCount = 2

WHILE @MyCount <= 5

BEGIN

UPDATE #MyHead

SET Top5NPA = t.Top5NPA+','+d.NPA

FROM #MyHead t,

(--Derived table "d" finds area codes by increasing rank

SELECT CustID,NPA

FROM #MyHead

WHERE GroupRank = @MyCount

) d

WHERE t.CustID = d.CustID

AND t.GroupRank = 1

SET @MyCount = @MyCount + 1

END

--===== Return the result set

SELECT *

FROM #MyHead

WHERE GroupRank = 1

ORDER BY RowNum

Here's what the first 20 lines of output look like... note that "CallCount" is still the number of calls for the single NPA which also happens to be the most used NPA for the given customer... GroupRank and RowNum mean nothing at this point...

RowNum CustID NPA CallCount GroupRank Top5NPA

----------- ----------- -------------------- ----------- ----------- --------------------

1 1000 248 243 1 248,869,517,352,941

17 1001 248 219 1 248,810,313,734,586

26 1005 248 93 1 248,586,425,813,989

31 1008 586 10 1 586

32 1010 734 9 1 734,904

34 1011 906 72 1 906

35 1016 906 80 1 906,317

37 1023 248 615 1 248,586,313,216,206

48 1066 330 15 1 330

49 1068 330 32 1 330,937,520,614,847

54 1073 330 196 1 330,734

56 1076 586 36 1 586,707,760,510,530

62 1089 330 306 1 330,304,775,216,949

73 1099 330 121 1 330,704

75 1102 330 43 1 330,234,216,615,646

80 1117 989 434 1 989,225,507,520,734

110 1124 586 87 1 586,248,212,734

114 1134 586 88 1 586,248,313,925

118 1141 734 237 1 734,517,956,615,770

131 1143 313 29 1 313,773

(20 row(s) affected)

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Gail-378561 SSCommitted Points: 1602 More actions · Answer 7

Jeff Thanks that worked great, Except How can i get that to work within an existing stored procedure

here is my Sproc that i am trying to implement this with.

Thanks again

Alter Procedure [dbo].[WeeklyMasterCallReport]

--

As

Declare @DateNow DateTime,

@Date5 DateTime

--set date to be yesterday

--SELECT DATEADD(dd, DATEDIFF(dd, 0, GETDATE() - 1), 0)

--set date to be yesterday

Set @DateNow = (SELECT DATEADD(dd, DATEDIFF(dd, 0, GETDATE() - 1), 0))

--set date -5

Set @Date5 =(SELECT DATEADD(dd, DATEDIFF(dd, 0, GETDATE() - 5), 0))

Exec TempAreaCodes --(This is the procedure Jeff M gave me)

--select distinct gtcalcdate from cdrmain where gtcalcdate between @Date7 and @DateNow

SELECT

Daily.GtCalcDate,

Callers.ClCallerName AS CallerNameID,

Callers.ClCallerNum,

Daily.TotLDCallsPerDay,

--More Columns.....

FROM

--Get Callers

(SELECT DISTINCT CallerName AS ClCallerName,CallerNum as ClCallerNum

FROM CDRMAIN

WHERE (CDRMAIN_4.CallerName = 'Caller3') OR

(CDRMAIN_4.CallerName = 'Caller2' OR

(CDRMAIN_4.CallerName = 'Caller3'))

) AS Callers INNER JOIN

(SELECT *

FROM #MyHead WHERE GroupRank = 1 )As DailyArea on DailyArea.CallerName = Callers.ClCallerName

Inner Join

--get daily local calls

(SELECT CDRMAIN_4.CallerName, CDRMAIN_4.GtCalcDate,SUM(TalkDuration) / 60 AS dylocTalkTimeTot,

SUM(CASE WHEN Direction = '2' AND LEN(Targetnum) >=7 AND LEN(Targetnum) =7 AND LEN(Targetnum) = 7 AND LEN(Targetnum) ='2006-07-01' and (LEN(TargetNum) > 6) AND (LEN(TargetNum) < 11) AND (DATEPART(Year, GtCalcDate) = DatePart(Year,@DateNow))

GROUP BY DATEPART(Year, GtCalcDate), CallerName

HAVING (CDRMAIN_4.CallerName = 'Caller3') OR

(CDRMAIN_4.CallerName = 'Caller2' OR

(CDRMAIN_4.CallerName = 'Caller3')) AS YearLoc ON YearLoc.yrlocCallerName = Callers.ClCallerName

GROUP BY Callers.ClCallerName, Daily.GtCalcDate,callers.ClCallerNum, Monthly.mnTotLDCallsPerMonth, Monthly.mnYear, Monthly.mnMonth, Yearly.yrTotLDCallsPerYear, Monthly.mnBusDayCount,

Yearly.yeardaycount, Daily.TotLDCallsPerDay, YearLoc.yrTotLocalCallsPerYear, YearLoc.yrlocTalkTimeTot,monthly.mnLDTalkTimeTot,yearly.yrLDTalkTimeTot

,Daily.dyLDTalkTimeTot,dailyloc.dyTotLocCallsPerDay

ORDER BY Callers.ClCallerName,daily.gtcalcdate,Monthly.mnYear, Monthly.mnMonth

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 8

I'm assuming that the proc you made from my code example creates a temp table within it... that won't work because of the scope of temp tables... you have to (at least) create the temp table in your existing procedure clear code. You might also want to add a couple of parameters to the new proc for date filters...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions