|
|
Posted by John Bell on 12/30/06 10:38
Hi Nassa
"Nassa" <nassim.czdashti@gmail.com> wrote in message
news:1167459738.395164.299970@v33g2000cwv.googlegroups.com...
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,FK: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
[Back to original message]
|