sql with pivot I think???

I am trying to list all the extensions for 1 phone call from phone data
each extension should be a column
I currently use max and min to get 2 of the columns but I want all
I think I need to do a pivot or something but dont know how
I am not sure how to put each UnitAddress from the multiple rows that
are associated with the phone number on 1 Row

it would be like

select

Phone
, MAX(UnitAddress)
, MIN(UnitAddress)
, SUM(CallLength)

from PhoneData

where Phone='212xxxYYYY'

group by

Phone


Note I need to sum of all the calls from 1 phone on 1 day together and then on that same row list all of the unitaddress instead of just max and min


any ideas?
LVL 2
pmtolk1Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

HwkrangerCommented:
You want a dynamic pivot.

Use this function that this team wrote:

http://weblogs.sqlteam.com/jeffs/articles/5120.aspx
0
Kevin CrossChief Technology OfficerCommented:
Does the list of extensions need to be in separate columns or would say a comma delimited list of extensions work just fine?

PIVOT would be good solution if you want the SUM by extension IMHO.

Since you do NOT in this case, I would avoid using PIVOT and the complexities associated with that; however, depending on your answer to my question, that may have to be a considered alternative.

If you are OK with a comma delimited listing, you can try this out as a solution:
SELECT Phone
, REPLACE((SELECT UnitAddress AS "data()" 
		FROM PhoneData e 
		WHERE e.Phone = p.Phone 
		FOR XML PATH(''))
	, ' ', ',') AS extensions
, SUM(CallLength)
FROM PhoneData p
WHERE Phone='212xxxYYYY'
GROUP BY Phone

Open in new window

0
pcelbaCommented:
Very nice. I would say this FOR XML side effect is neglected(unsure if is it the right word) in most cases. Are you sure it does not report "missing aggregate function" error? I have to test it.

Hope UnitAddress does not contain spaces... :-)
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Kevin CrossChief Technology OfficerCommented:
Yeah, I think I learned that trick from CGLuttrell.

For potential spaces, can just LTrim(RTrim(UnitAddress)).  Also could have duplicate records to represent multiple calls logged, so here an updated version.

It doesn't need aggregate since it is a sub query that only uses Phone which is included in the group by clause.  Would have errors if had to join on some other field like CallLength that is being aggregated.
-- cte used for test purposes, can remove for live query
;WITH PhoneData(Phone, UnitAddress, CallLength) AS (
	SELECT '212xxxYYYY', '  345 ', 1.2
	UNION ALL SELECT '212xxxYYYY', '123', 2.4
	UNION ALL SELECT '212xxxYYYY', '212', 1.1
	UNION ALL SELECT '212xxxYYYY', '123', 0.4
	UNION SELECT '212xxzYYYY', '  345 ', 1.2
	UNION ALL SELECT '212xxzYYYY', '123', 2.4
	UNION ALL SELECT '212xxqYYYY', '212', 1.1
	UNION ALL SELECT '212xxzYYYY', '123', 0.4
	
) -- end cte used for test purposes
SELECT Phone
, REPLACE((SELECT DISTINCT LTrim(RTrim(UnitAddress)) AS "data()" 
                FROM PhoneData e 
                WHERE e.Phone = p.Phone 
                FOR XML PATH(''))
        , ' ', ',') AS extensions
, SUM(CallLength)
FROM PhoneData p
-- commented to show works on multiple rows without impacting aggregate
--WHERE Phone='212xxxYYYY'
GROUP BY Phone

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Chris LuttrellSenior Database ArchitectCommented:
mwvisa1, thanks for the plug :-)  
Nice to know folks learn from what I have contributed.
Congrats on your Good Answer here!
0
Kevin CrossChief Technology OfficerCommented:
Most definitely.
I like this approach much better than using variable and so having to do in multi-part statement.  You inspired me to look more deeply into how it works and actually used it in reverse for splitting comma delimited string to table/list.

Regards,
--isa
0
pmtolk1Author Commented:
mwvisa1: I am running your query and it is taking over two minutes now
I am getting some weird kind of combinatorial explosion
How can I filter the results better so it doesnt explode?

Thanks so much
0
Kevin CrossChief Technology OfficerCommented:
Sorry, I missed the fact that you said this but didn't have in your code an actual filter:
"Note I need to sum of all the calls from 1 phone on 1 day together"

If that is truly the case, then you should probably have this as your group by:
GROUP BY Phone, CallDate
Where "CallDate" should be replaced with your column containing the date you want to filter on.

With that said, you will probably want to take that a step further and filter your subquery for extensions to only pull the extensions that correspond to phone for that same date.
SELECT Phone, CallDate
, REPLACE((SELECT DISTINCT LTrim(RTrim(UnitAddress)) AS "data()" 
                FROM PhoneData e 
                WHERE e.Phone = p.Phone AND e.CallDate = p.CallDate
                FOR XML PATH(''))
        , ' ', ',') AS extensions
, SUM(CallLength)
FROM PhoneData p
-- remember I commented this to test, you had uncommented:
WHERE Phone='212xxxYYYY'
GROUP BY Phone, CallDate

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.