Query to represent denormalization of data

Hi All,

Bit of a funny one. I have two tables, lets call them PetOwner and Pets

PetOwner

OwnerID OwnerName
1 Tim
2 Paul
3 Andy

Pets
OwnerID PetName
1 Tricia
1 Louise
2 Bill
2 Ben
2 David
2 Topsy
3 Flopsy
3 Mopsy

And I need to write a query that returns a resultset like this

OwnerID OwnerName PetName1 PetName2 PetName3 PetName4 PetName5
1 Tim Tricia Louise NULL NULL NULL
2 Paul Bill Ben David Topsy Flopsy
3 Andy Flopsy Mopsy NULL NULL NULL

Can anyone help?

N.B. A pet owner can only have a maximum of 5 pets, but even if no-one in the database has 5 pets, the PetName5 column must still appear (but have a value of NULL).Is this SQL 2000 or 2005?|||Doh! Sorry, SQL Server 2005.|||No it's not, it's 2000! (soon to be moved to 2005)|||No probs :)

And absolutely always a max of five? This affects the best\ easiest solution...|||Soon enouigh to accept a 2005 answer? ;)|||This should work in 2000 and 2005. There is a more efficient 2005 method so you can update it when you migrate.


SELECT po.OwnerID
, po.OwnerName
, MAX(CASE WHEN rn = 1 THEN PetName END) AS PetName1
, MAX(CASE WHEN rn = 2 THEN PetName END) AS PetName2
, MAX(CASE WHEN rn = 3 THEN PetName END) AS PetName3
, MAX(CASE WHEN rn = 4 THEN PetName END) AS PetName4
, MAX(CASE WHEN rn = 5 THEN PetName END) AS PetName5
FROM (SELECT *
, (SELECT COUNT(*) AS rec_no
FROM dbo.pets AS p2
WHERE p2.OwnerID = p.OwnerID
AND p2.PetName <= p.PetName) AS rn
FROM dbo.pets AS p) AS row_numbers
INNER JOIN
dbo.PetOwner AS po
ON po.OwnerID = row_numbers.OwnerID
GROUP BY po.OwnerID
, po.OwnerName|||That's great, I'll test it and let you know how I get on.

Cheers,|||Ok, I'm british so I shouldn't say this, but pootle flump, you rock! Thanks, it works a dream|||Ok, I'm british so I shouldn't say this, but pootle flump, you rock! Thanks, it works a dreamI'm British too so Jolly Good Show!|||Don't British people rock too? Elton? Ziggie? Mick?|||Don't British people rock too? Elton? Ziggie? Mick?... Poots?|||Elton... Rock... Rock... Elton?
Seriously!|||Elton "crocodile" rocks...