April quiz - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Site Related Forums

The Yak Corral

April quiz

Author

Topic

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-16 : 17:04:24

For an april quiz, I come across this situation.
In this scenario you must imagine you are a "pet shrink", like Cesar Millan.

You have a table where you keep track of pet owners and their pets for group consultations.
A single group consultation is named "Set".

Every group consultation can have a maximum of three pet owners and a maximum of three pets.
A pet owner can have more than one pet and more than one people can be the owner of a pet.

This is the sample data

-- Prepare sample data
DECLARE	@Sample TABLE ([Set] VARCHAR(50), [Name] VARCHAR(50), [Owner] BIT)

INSERT	@Sample
SELECT	1, 'kim',    1 UNION ALL 
SELECT	1, 'fluffy', 0 UNION ALL 
SELECT	2, 'mark',   1 UNION ALL
SELECT	2, 'fido',   0 UNION ALL
SELECT	3, 'tina',   1 UNION ALL
SELECT	3, 'tracey', 1 UNION ALL
SELECT	3, 'bob',    0 UNION ALL
SELECT	5, 'kate',   1 UNION ALL
SELECT	5, 'trixi',  0 UNION ALL
SELECT	5, 'speed',  0

There can never be more than one pet owner with same name within same set,
and there can never be more than one pet with same name within same set.
This is the same as having a primary key over [Set], [Name], [Owner].

The quiz is to write a query that produces a resultset like the following.
Order of pet owners and pets must be ascending.

In SQL Server 2005 this is easily done by running this query

-- SQL Server 2005 solution
SELECT		p.[Set], 
		MAX(CASE WHEN p.ColID = 1 AND p.[Owner] = 1 THEN p.[Name] ELSE NULL END) AS Owner1, 
		MAX(CASE WHEN p.ColID = 2 AND p.[Owner] = 1 THEN p.[Name] ELSE NULL END) AS Owner2, 
		MAX(CASE WHEN p.ColID = 3 AND p.[Owner] = 1 THEN p.[Name] ELSE NULL END) AS Owner3, 
		MAX(CASE WHEN p.ColID = 1 AND p.[Owner] = 0 THEN p.[Name] ELSE NULL END) AS Pet1, 
		MAX(CASE WHEN p.ColID = 2 AND p.[Owner] = 0 THEN p.[Name] ELSE NULL END) AS Pet2, 
		MAX(CASE WHEN p.ColID = 3 AND p.[Owner] = 0 THEN p.[Name] ELSE NULL END) AS Pet3 
FROM		( 
			SELECT	[Set], 
				[Name], 
				[Owner], 
				ROW_NUMBER() OVER (PARTITION BY [Set], [Owner] ORDER BY [Name]) AS ColID 
			FROM	@Sample
		) AS p 
GROUP BY	p.[Set]

The quiz is, "How would a single query solution for SQL Server 2000 look like?".

-- SQL Server 2000 solution?

Please don't search the Internet.
I already have posted a solution for the original poster.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-17 : 16:25:29

No one want to participate?

Well then, here is my suggestion

SELECT		y.[Set], 
		y.Owner1, 
		CASE 
			WHEN y.Owner1 = y.Owner2 THEN s.[Name] 
			ELSE y.Owner2 
		END AS Owner2, 
		CASE 
			WHEN y.Owner1 = y.Owner2 THEN NULL 
			ELSE s.[Name] 
		END AS Owner3, 
		y.Pet1, 
		CASE 
			WHEN y.Pet1 = y.Pet2 THEN s.[Name] 
			ELSE y.Pet2 
		END AS Pet2, 
		CASE 
			WHEN y.Pet1 = y.Pet2 THEN NULL 
			ELSE s.[Name] 
		END AS Pet3 
FROM		( 
			SELECT		[Set], 
					MIN(CASE WHEN [Owner] = 1 THEN [Name] ELSE NULL END) AS Owner1, 
					MAX(CASE WHEN [Owner] = 1 THEN [Name] ELSE NULL END) AS Owner2, 
					MIN(CASE WHEN [Owner] = 0 THEN [Name] ELSE NULL END) AS Pet1, 
					MAX(CASE WHEN [Owner] = 0 THEN [Name] ELSE NULL END) AS Pet2 
			FROM		@Sample 
			GROUP BY	[Set] 
		) AS y 
LEFT JOIN	@Sample AS s ON s.[Set] = y.[Set] 
			AND	( 
					s.[Name] NOT IN (y.Owner1, y.Owner2) 
					AND s.[Owner] = 1 
				OR 
					s.[Name] NOT IN (y.Pet1, y.Pet2) 
					AND s.[Owner] = 0 
				)

E 12°55'05.25"
N 56°04'39.16"

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-04-24 : 08:14:06

Hi Peso

I've just seen this, and here's my effort (before I looked at your suggestion).

select
    a.[Set],
    max(a.[Owner1]) as Owner1,
    max(case when NumberOfOwners = 2 then FinalOwner when NumberOfOwners = 3 then nullif(nullif(Name, Owner1), FinalOwner) end) as Owner2,
    max(case when NumberOfOwners = 3 then FinalOwner end) as Owner3,
    max(a.[Pet1]) as Pet1,
    max(case when NumberOfPets = 2 then FinalPet when NumberOfPets = 3 then nullif(nullif(Name, Pet1), FinalPet) end) as Pet2,
    max(case when NumberOfPets = 3 then FinalPet end) as Pet3
from (
    select [Set],
        min(case when Owner = 1 then Name end) as Owner1,
        max(case when Owner = 1 then Name end) as FinalOwner,
        sum(case when Owner = 1 then 1 end) as NumberOfOwners,
        min(case when Owner = 0 then Name end) as Pet1,
        max(case when Owner = 0 then Name end) as FinalPet,
        sum(case when Owner = 0 then 1 end) as NumberOfPets
    from @Sample a group by [Set]) a inner join @Sample b on a.[Set] = b.[Set]
group by a.[Set]

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-04-24 : 08:22:56

Another possibility is to do the ROW_NUMBER() bit of the 2005 version in the usual 2000 way. This will be less efficient though, I'm sure.

SELECT		p.[Set], 
		MAX(CASE WHEN p.ColID = 1 AND p.[Owner] = 1 THEN p.[Name] ELSE NULL END) AS Owner1, 
		MAX(CASE WHEN p.ColID = 2 AND p.[Owner] = 1 THEN p.[Name] ELSE NULL END) AS Owner2, 
		MAX(CASE WHEN p.ColID = 3 AND p.[Owner] = 1 THEN p.[Name] ELSE NULL END) AS Owner3, 
		MAX(CASE WHEN p.ColID = 1 AND p.[Owner] = 0 THEN p.[Name] ELSE NULL END) AS Pet1, 
		MAX(CASE WHEN p.ColID = 2 AND p.[Owner] = 0 THEN p.[Name] ELSE NULL END) AS Pet2, 
		MAX(CASE WHEN p.ColID = 3 AND p.[Owner] = 0 THEN p.[Name] ELSE NULL END) AS Pet3 
FROM		( 
			SELECT	[Set], 
				[Name], 
				[Owner], 
                (select count(*) from @Sample where [Set] = a.[Set] and [Owner] = a.[Owner] and Name <= a.Name) as ColID
			FROM	@Sample a
		) AS p 
GROUP BY	p.[Set]

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources