Solved

sql with pivot I think???

Posted on 2009-07-16
8
283 Views
Last Modified: 2012-05-07
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?
0
Comment
Question by:pmtolk1
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 9

Assisted Solution

by:Hwkranger
Hwkranger earned 165 total points
ID: 24872405
You want a dynamic pivot.

Use this function that this team wrote:

http://weblogs.sqlteam.com/jeffs/articles/5120.aspx
0
 
LVL 59

Assisted Solution

by:Kevin Cross
Kevin Cross earned 335 total points
ID: 24872418
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
 
LVL 42

Expert Comment

by:pcelba
ID: 24873184
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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 59

Accepted Solution

by:
Kevin Cross earned 335 total points
ID: 24873634
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
 
LVL 26

Expert Comment

by:Chris Luttrell
ID: 24874077
mwvisa1, thanks for the plug :-)  
Nice to know folks learn from what I have contributed.
Congrats on your Good Answer here!
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 24874111
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
 
LVL 2

Author Comment

by:pmtolk1
ID: 24874183
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
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 24874236
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

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Stored Proc - Rewrite 42 73
SSMS Imprt data from Excel 7 27
SQL syntax question 6 43
T-SQL: I need to add an index on a field 5 27
Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

733 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