Need to pull mulitple row communication infor into single row per customer

Question

Need to pull mulitple row communication infor into single row per customer

j2associates

SSC Veteran

Points: 277
More actions
October 21, 2009 at 5:44 pm

#138962

Hello all,
What is the best way to accomplish the following? We have a communication table that has CommType and CommAddress columns. CommType holds values such as Phone, eMail, Fax and AltPhone. CommAddress then holds the corresponding value. Following is some sample data:
PrimaryKey CommType CommAddress
1234 Phone Null
2456 Phone 1234567
2456 eMail somebody@somewhere.com
4567 Fax 1234567890
4567 Phone 1234569870
What is the best way to retrieve all of the corresponding communications information for a given primary key?
Possible View
SELECT c.EntityKey,
ISNULL(f.Fax, '') AS Fax,
ISNULL(p.Phone, '') AS Phone,
ISNULL(ap.AltPhone, '') AS AltPhone,
ISNULL(e.eMail, '') AS eMail
FROM
(SELECT DISTINCT EntityKey FROM dbo.Communication) c LEFT JOIN
(SELECT CommAddress AS Fax, PKey, EntityKey
FROM dbo.Communication WHERE CommType = 'Fax') AS f
ON f.EntityKey = c.EntityKey LEFT JOIN
(SELECT CommAddress AS eMail, PKey, EntityKey
FROM dbo.Communication WHERE CommType = 'eMail') AS e
ON e.EntityKey = c.EntityKey LEFT JOIN
(SELECT CommAddress AS Phone, PKey, EntityKey
FROM dbo.Communication WHERE CommType = 'Phone') AS p
ON p.EntityKey = c.EntityKey LEFT JOIN
(SELECT CommAddress AS AltPhone, PKey, EntityKey
FROM dbo.Communication WHERE CommType = 'AltPhone') AS ap
ON ap.EntityKey = c.EntityKey
Possible table valued function where DNR.ifnFormatPhone returns a formatted phone/fax number or an empty string
ALTER FUNCTION [DNR].[ifnGetEntityCommunicationInfo] (
@EntityKey int,
@Formatted bit = 1 )
RETURNS TABLE
AS RETURN (
SELECT c.EntityKey,
CASE @Formatted WHEN 1 THEN [DNR].ifnFormatPhone(f.Fax) ELSE f.Fax END AS Fax,
CASE @Formatted WHEN 1 THEN [DNR].ifnFormatPhone(p.Phone) ELSE p.Phone END AS Phone,
CASE @Formatted WHEN 1 THEN [DNR].ifnFormatPhone(ap.AltPhone) ELSE ap.AltPhone END AS AltPhone,
ISNULL(e.eMail, '') AS eMail
FROM(SELECT DISTINCT EntityKey FROM dbo.Communication) c LEFT JOIN
(SELECT CommAddress AS Fax, EntityKey FROM dbo.Communication WHERE CommType = 'Fax') AS f ON f.EntityKey = c.EntityKey LEFT JOIN
(SELECT CommAddress AS eMail, EntityKey FROM dbo.Communication WHERE CommType = 'eMail') AS e ON e.EntityKey = c.EntityKey LEFT JOIN
(SELECT CommAddress AS Phone, EntityKey FROM dbo.Communication WHERE CommType = 'Phone') AS p ON p.EntityKey = c.EntityKey LEFT JOIN
(SELECT CommAddress AS AltPhone, EntityKey FROM dbo.Communication WHERE CommType = 'AltPhone') AS ap ON ap.EntityKey = c.EntityKey
WHERE c.EntityKey = @EntityKey )
I know how to join the view, but for the table valued function, I think I would use Outer Apply to join to other tables but I'm fuzzy on that. Are there other ways to do this better? I'm not a dba so are there performance issues for any of the solutions? Thanks in advance for any ideas and/or suggestions!

Viewing 7 posts - 1 through 6 (of 6 total)

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

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 1

If you want performance, don't use a MUCK/EAV for one thing. 😉

Seriously, this type of table does not scale well and you will find yourself jumping through hoop after hoop to make it work for you. If you know that you are going to have several potential CommTypes, just create a table for them with proper column names: PRIMARYKEY|BUSPHONE|FAX|CELLPHONE|HOMEPHONE|EMAIL

But if you are already stuck with one, and you want to flatten it out, you need to either read up on PIVOT/UNPIVOT or do a little crosstab/case coding (first hoop). Notice that I had to know what the possible values of CommType were in advance. The same holds true for PIVOT/UNPIVOT. If you say that users can dynamically create new commtypes on the fly, then you will have to resort to dynamic SQL (second hoop).

Do a little searching on this site and you will probably find a dynamic SQL example for a problem very similar to this.

declare @sample table (PrimaryKey int, CommType varchar(30), CommAddress varchar(50))

insert into @sample

select 1234, 'Phone', Null union all

select 2456, 'Phone', '1234567' union all

select 2456, 'eMail', 'somebody@somewhere.com' union all

select 4567, 'Fax', '1234567890' union all

select 4567, 'Phone', '123456987'

-- crosstab example

select PrimaryKey

,max(case when commtype = 'Phone' then CommAddress else '' end) as Phone

,max(case when commtype = 'Email' then CommAddress else '' end) as eMail

,max(case when commtype = 'Fax' then CommAddress else '' end) as Fax

from @sample

-- where PrimaryKey =

group by PrimaryKey

order by PrimaryKey

By the way, please take note of how I set up a script to declare a sample table variable and populate it with your data. If you do this next time, instead of simply cutting and pasting, people will jump on your problem a lot faster. Thanks.

Let me know if you have any questions.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

j2associates SSC Veteran Points: 277 More actions · Answer 2

Hello Bob,

Many thanks for taking the time to respond! Thanks also for the script suggestion. I will do so in the future. What are muck/eav acronyms?

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 3

"EAV" stands for Entity-Attribute-Value table, also derogatorily referred to as a "MUCK". (Massively Unified Code-Key table) While some people gravitate towards them because of their flexible nature, they have downsides as well. Here are a couple of articles on the subject, and you can find more if you search this site using EAV and/or MUCK as your search argument(s).

http://www.sqlservercentral.com/articles/Advanced/lookuptablemadness/1464/

http://www.sqlservercentral.com/articles/Database+Design/62386/

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Jeff Moden SSC Guru Points: 1004372 More actions · Answer 4

BWAA-HAA!!! What downside? Just think of it... you store everything in one table and you only need one index. Simplicity at it's best. :-P:-D;-)

(For those not familiar with the "dark side", I'm kidding. ;-))

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

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 5

It's not simpler than

CREATE dbo.DoomsdayTable (data xml) 😎

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

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

Heh... does it like pork chops? 😛

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