June 8, 2010 at 10:33 am
Hi,
I would like to know if it is possible to do a cross tab on data which typically cannot be aggregated.
For this sample data I would like to display one row with a column for each ContactNumberType.
In the past I would do a self join for each contact number type but this can get messy.
CREATE TABLE #tempContactNumbers
(
CLID int,
ClientName varchar(50),
ContactType varchar (50),
ContactNumber varchar (50)
)
INSERT INTO #tempContactNumbers
SELECT1, 'Bob', 'Home', '5556666'
UNION ALL
SELECT 1, 'Bob', 'Work', '7778888'
UNION ALL
SELECT 1, 'Bob', 'Cell', '9991111'
Below are the two approaches I'm currently working on.
Approach 1 is the direction I'm trying to take this, Approach 2 is what I currently have in place but would like to replace with a better way.
--Approach 1: Preferred Method but undesired result?
SELECT
ClientName,
CASE WHEN ContactType = 'Home' THEN ContactNumber ELSE 0 END AS [HomeNumber],
CASE WHEN ContactType = 'Work' THEN ContactNumber ELSE 0 END AS [WorkNumber],
CASE WHEN ContactType = 'Cell' THEN ContactNumber ELSE 0 END AS [CellNumber]
FROM #tempContactNumbers
--GROUP BY??
--Approach 2: CurrentMethod preferred result
SELECT DISTINCT
a.ClientName,
b.ContactNumber AS [HomeNumber],
c.ContactNumber AS[WorkNumber],
d.ContactNumber AS[CellNumber]
FROM #tempContactNumbers as a
LEFT JOIN #tempContactNumbers AS b
ON a.CLID = b.CLID
AND b.ContactType ='Home'
LEFT JOIN #tempContactNumbers AS c
ON a.CLID = c.CLID
AND c.ContactType ='Work'
LEFT JOIN #tempContactNumbers AS d
ON a.CLID = d.CLID
AND d.ContactType ='Cell'
Any suggestions would be appreciated.
G
June 8, 2010 at 11:09 am
I can think of two ways:
method #1
select ClientName, [Home], [Work], [Cell]
from #tempContactNumbers
pivot
(
MIN (ContactNumber)
FOR ContactType IN
([Home], [Work], [Cell])
) pvt;
method #2
with cteTemp(ClientName, Home, Work, Cell)
as
(
SELECT
ClientName,
CASE WHEN ContactType = 'Home' THEN ContactNumber END,
CASE WHEN ContactType = 'Work' THEN ContactNumber END,
CASE WHEN ContactType = 'Cell' THEN ContactNumber END
FROM #tempContactNumbers
)
select ClientName,
MAX(Home) as Home,
MAX(Work) as Work,
MAX(Cell) as Cell
from cteTemp
group by ClientName;
On an extremely small amount of data, method #1 should be faster but I'm not sure if there would be a point at which method #2 would be more efficient. You should test on your own system to see.
June 8, 2010 at 2:51 pm
SELECT
ClientName,
MAX(CASE WHEN ContactType = 'Home' THEN ContactNumber ELSE 0 END) AS [HomeNumber],
MAX(CASE WHEN ContactType = 'Work' THEN ContactNumber ELSE 0 END) AS [WorkNumber],
MAX(CASE WHEN ContactType = 'Cell' THEN ContactNumber ELSE 0 END) AS [CellNumber]
FROM #tempContactNumbers
GROUP BY Clientname
Scott Pletcher, SQL Server MVP 2008-2010
June 8, 2010 at 3:53 pm
Thanks both for the code samples, it is greatly appreciated!
June 8, 2010 at 4:06 pm
Scott has it sussed above... almost everyone forgets that aggregations include MAX to handle character based cross tabs. The only thing you may want to (probably should) change in his example is to change the ELSE 0 to ELSE ''.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 8, 2010 at 4:09 pm
Quite true about using "ELSE 0", and I was tempted to make that change myself, but stuck to the original requestor's original code :-).
Scott Pletcher, SQL Server MVP 2008-2010
June 8, 2010 at 4:20 pm
Agreed and sorry Scott. I didn't word my post quite correctly.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply