Posted by Erland Sommarskog on 12/30/06 16:48

John Bell (jbellnewsposts@hotmail.com) writes:
> But,I want a query which shows the results in a way below:
> (id,Name,tellephone,mobile,Fax,e-mail,address,comment)
> (4,nassa,071163738,091463738,Null,Null,XXX,Null)
>
>
> SELECT b.id, b.name, n1.telno AS [Tellphone], n2.telno AS [Mobile],
> n3.telno AS [Fax], b.address, b.comment
> FROM Telbook b
> LEFT JOIN ( TelNumbers n1
> JOIN Teltypes t1 ON t1.fk = n1.telNotype AND t1.teltypes = 'Tellphone' )
> on n1.telbookid = b.id
> LEFT JOIN ( TelNumbers n2
> JOIN Teltypes t2 ON t2.fk = n2.telNotype AND t2.teltypes = 'Mobile' ) on
> n2.telbookid = b.id
> LEFT JOIN ( TelNumbers n3
> JOIN Teltypes t3 ON t3.fk = n3.telNotype AND t3.teltypes = 'Fax' ) on
> n3.telbookid = b.id

John, here is a way to write that query with a single join:

SELECT b.id, b.Name, a.Tellphone, a.Mobile, a.Fax, b.address, b.comment
FROM Telbook b
JOIN (SELECT n.telbookid,
Tellphone = MAX(CASE WHEN t.teltypes = 'Tellphone'
THEN n.telno
END),
Mobile = MAX(CASE WHEN t.teltypes = 'Mobile'
THEN n.telno
END),
Fax = MAX(CASE WHEN t.teltypes = 'Fax'
THEN n.telno
END)
FROM TelNumbers n
JOIN Teltypes t ON n.telNotype = t.fk
GROUP BY n.telbookid) AS a ON a.telbookid = b.id

This is, in my opinion, *the* way to write a pivot query. It's uses
ANSI SQL, so it has a chance of being portable. And once you have learnt
the principle, it's easy to remember.

The trick is the use of MAX. Each CASE expression will return at most
one non-NULL value. So whether we use MAX or MIN does not matter

The syntax with the PIVOT keyword introduced in SQL 2005, on the other
hand, is useless in my opinion. The syntax is not any more compact than
the above, nor even any easier to use or remember. And top of that it's
propritary and not portable. Possibly, it helps the optimizer so a query
with PIVOT could execute faster than using CASE and GROUP BY. Personally,
I haven't even bothered to learn the PIVOT/UNPIVOT syntax.

Also, the PIVOT query you posted, will not work for Nassa because he/she
uses ntext which cannot be used with PIVOT. I see in your tables that
you use nvarchar(MAX), but I tried yesterday in
microsoft.public.sqlserver.programming to convince Nassa to use
nvarchar(MAX) instead, but he/she did not even comment on it.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]