> I have 3 tables:
> Telbook(Id:int,Name:char,address:char,comment:ntext,owneruserid:int),
> PK:id
> TelNumbers(telbookid:int,telno:char,telNotype:int,syscode:int),
> PK:syscode,F­K:telNumbers.telbookid=telbook.id
>
> Teltypes(teltypes:char,fk:int),FK:telnumbers.telnotype=teltypes.fk
>
>
> The question is here that I can create a query which results are:
> (id,Name,telno,telnotype,teltypes,address,comment)
> (4,nassa,091463738,2,Mobile,XXX,Null)
> (4,nassa,071163738,1,Tellphone,XXX,Nul)
>
>
> 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)
>
> would you mind telling me how to solve it with PIVOT?

Erland Sommarskog wrote:
> Nassa (nassim.czdashti@gmail.com) writes:
> > I have 3 tables:
> > Telbook(Id:int,Name:char,address:char,comment:ntext,owneruserid:int),
> > PK:id
> > TelNumbers(telbookid:int,telno:char,telNotype:int,syscode:int),
> > PK:syscode,F=ADK:telNumbers.telbookid=3Dtelbook.id
> >
> > Teltypes(teltypes:char,fk:int),FK:telnumbers.telnotype=3Dteltypes.fk
> >
> >
> > The question is here that I can create a query which results are:
> > (id,Name,telno,telnotype,teltypes,address,comment)
> > (4,nassa,091463738,2,Mobile,XXX,Null)
> > (4,nassa,071163738,1,Tellphone,XXX,Nul)
> >
> >
> > 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)
> >
> > would you mind telling me how to solve it with PIVOT?
>
> From a post that I did in the same thread in comp.databases.ms-sqlserver:
>
> SELECT b.id, b.Name, a.Tellphone, a.Mobile, a.Fax, b.address, b.comment
> FROM Telbook b
> JOIN (SELECT n.telbookid,
> Tellphone =3D MAX(CASE WHEN t.teltypes =3D 'Tellphone'
> THEN n.telno
> END),
> Mobile =3D MAX(CASE WHEN t.teltypes =3D 'Mobile'
> THEN n.telno
> END),
> Fax =3D MAX(CASE WHEN t.teltypes =3D 'Fax'
> THEN n.telno
> END)
> FROM TelNumbers n
> JOIN Teltypes t ON n.telNotype =3D t.fk
> GROUP BY n.telbookid) AS a ON a.telbookid =3D 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.
>
> And since you insist on using ntext, PIVOT is going to be difficult
> anyway.
>
> --
> 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