PIVOT for non-aggregate data?

I've used UNPIVOT for cleaning up data from tables that violate 1NF.
However, the PIVOT operator seems to behave a little differently, and I
guess I may have misunderstood it from using UNPIVOT (I know they're
not exact complements, in spite of their names). Could someone tell me
if I'm right on this? It seems that PIVOT is only useful in aggregates.
In other words, with the following data (schema can be inferred to
point of relevance):
-- Assume that it's known each person will have a maximum of three
cars. This isn't my exact problem, but can illustrate the conceptual
hurdle.
PersonName AutomobileNumber MakeModel
---
Joe 1 Peugeot505
Joe 2 HondaAccord
Joe 3 LincolnMarkIII
Bob 1 HondaAccord
Bob 2 DMCCoupe
and I want this result (in SQL 2000, I would build this w/ temp tables
and/or joins)
PersonName Automobile1 Automobile2 Automobile3
-----
Joe Peugeot505 HondaAccord
LincolnMarkIII
Bob HondaAccord DMCCoupe NULL
I assume PIVOT isn't really supposed to be used like this, is it? It's
only for aggregates. Is that right? It'd be nice if the following
worked (and it may be syntactically incorrect as it sits, so forgive
me):
SELECT PersonName, [1] AS Automobile1, [2] AS Automobile2, [3] AS
Automobile3
FROM PeoplesCars PIVOT (MakeModel FOR AutomobileNumber IN ([1], [2],
[3])) x
-AlanMy apologies to posting to sqlserver.server -- it's late. I was
thinking I was in sqlserver.programming
-Alan|||Alan
CREATE TABLE Foo
(
PersonName NVARCHAR(30) NOT NULL,
AutomobileNumber INT NOT NULL,
MakeModel NVARCHAR(30) NOT NULL
)
INSERT INTO Foo VALUES( N'Joe',1, 'Peugeot505')
INSERT INTO Foo VALUES( N'Joe',2, 'HondaAccord')
INSERT INTO Foo VALUES( N'Joe',3, 'LincolnMarkIII')
INSERT INTO Foo VALUES( N'Bob',1, 'HondaAccord')
INSERT INTO Foo VALUES( N'Bob',2, 'DMCCoupe')
SELECT PersonName,
MAX(CASE WHEN AutomobileNumber =1 THEN MakeModel END ) Automobile1,
MAX(CASE WHEN AutomobileNumber =2 THEN MakeModel END ) Automobile2,
MAX(CASE WHEN AutomobileNumber =3 THEN MakeModel END) Automobile3
FROM foo
GROUP BY PersonName
"Alan Samet" <alansamet@.gmail.com> wrote in message
news:1147588956.476925.259410@.i39g2000cwa.googlegroups.com...
> My apologies to posting to sqlserver.server -- it's late. I was
> thinking I was in sqlserver.programming
> -Alan
>|||Alan Samet wrote:
> My apologies to posting to sqlserver.server -- it's late. I was
> thinking I was in sqlserver.programming
> -Alan
This works:
SELECT PersonName,
[1] AS Automobile1, [2] AS Automobile2, [3] AS Automobile3
FROM PeoplesCars
PIVOT (MIN(MakeModel) FOR AutomobileNumber IN ([1], [2], [3])) x ;
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--