Hi everyone,

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)

I run SQL server 2005, and I want to use PIVOT but I dnt know how!.

Thanks,
Nassa

Check out http://www.aspfaq.com/etiquette.asp?id=5006 on how to post DDL and
Sample data that is useful such as

CREATE TABLE Telbook(Id int not null constraint PK_telbook PRIMARY KEY,

[Name] varchar(20),

[address] varchar(60),

comment nvarchar(MAX),

owneruserid int)

CREATE TABLE Teltypes(teltypes varchar(30),

[fk] int not null constraint PK_Teltypes PRIMARY KEY)

CREATE TABLE TelNumbers(

telbookid int NOT NULL CONSTRAINT FK_TelNumbers_Telbook FOREIGN KEY
REFERENCES Telbook(id),

telno char(15),

telNotype int CONSTRAINT FK_TelNumbers_Teltypes FOREIGN KEY REFERENCES
Teltypes(fk),

syscode int not null CONSTRAINT PK_TelNumbers PRIMARY KEY)

INSERT INTO Teltypes(teltypes, [fk] )

SELECT 'Tellphone', 0

UNION ALL SELECT 'Mobile', 1

UNION ALL SELECT 'Fax', 2

INSERT INTO Telbook(Id, [Name], [address], comment, owneruserid)

SELECT 1, 'Fred', 'The Burrow', NULL, 1

UNION ALL SELECT 2, 'George', 'The Burrow', NULL, 2

UNION ALL SELECT 3, 'Ron', 'The Burrow', NULL, 3

UNION ALL SELECT 4, 'Ginny', 'The Burrow', NULL, 4

UNION ALL SELECT 5, 'Percy', 'The Burrow', NULL, 5

INSERT INTO TelNumbers(telbookid, telno, telNotype, syscode)

SELECT 1, '0921323123', 1, 1

UNION ALL SELECT 1, '0123323123', 0, 2

UNION ALL SELECT 2, '0123323123', 0, 3

UNION ALL SELECT 3, '0123323123', 0, 4

UNION ALL SELECT 4, '0123323123', 0, 5

UNION ALL SELECT 5, '0123323123', 0, 6

UNION ALL SELECT 2, '0944423123', 1, 7

UNION ALL SELECT 5, '0955423123', 1, 8

UNION ALL SELECT 5, '0123323125', 2, 9

It would have been useful to post the query you did have for:

The question is here that I can create a query which results are:
(id,Name,telno,telnotype,teltypes,address,comment)

Such as:

SELECT b.id, b.name, n.telno, n.telnotype, t.teltypes, b.address, b.comment

FROM Telbook b

JOIN TelNumbers n on n.telbookid = b.id

JOIN Teltypes t ON t.fk = n.telNotype

And for the following query, if you have set types you can self join instead
of using a Pivot such as

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

But if you do want to use a pivot try something like:

SELECT id, name, [Tellphone], [Mobile], [Fax], address, comment

FROM

( SELECT b.id, b.name, b.address, b.comment, t.teltypes, n.telno

FROM Telbook b

JOIN TelNumbers n on n.telbookid = b.id

JOIN Teltypes t ON t.fk = n.telNotype

) AS SourceTable

PIVOT

(

MAX(telno)

FOR teltypes IN ( [Tellphone], [Mobile], [Fax] )

) AS pvt

ORDER BY id

John