SQL Matrix

SQL code (of the MSSQL variety) to do a matrix joining two tables

MSSQL doesn't have a LIMIT with start & count to get a specific record from a result set, so we use the trick from _CodeJack_

"ORDER BY Roommates" in the subqueries can be anything that will give consistent order; doesn't have to be an id

Sample data:

housingrequest
id	date	...
1	1/1/2007	...
2	1/1/2007	...
3	1/2/2007	...
4	1/2/2007	...

housingrequest_roommates
Request	Roommates
1	35235
2	88482
2	120
2	4472
4	9942
4	80113

SQL

select
/* column 1 */
(select TOP 1 Roommates from housingrequest_roommates
	where Request = r.id
	ORDER BY Roommates) as roommate1,
/* column 2 */
CASE WHEN (select count(*) from housingrequest_roommates where Request = r.id ) >= 2 THEN
	(select TOP 1 Roommates from (select TOP 2 Roommates from housingrequest_roommates
			where Request = r.id
			ORDER BY Roommates desc) as tmp
		ORDER BY Roommates
		)
ELSE null END as roommate2,
/* column 3 */
CASE WHEN (select count(*) from housingrequest_roommates where Request = r.id ) >= 3 THEN
	(select TOP 1 Roommates1 from (select TOP 3 Roommates from housingrequest_roommates
			where Request = r.id
			ORDER BY Roommates desc) as tmp
		ORDER BY Roommates
		)
ELSE null END as roommate3,
/* more columns */
*
from housingrequest r

Sample output:

roommate1	roommate2	roommate3	id	date	...
35235	NULL	NULL	1	1/1/2007	...
120	4472	88482	2	1/1/2007	...
NULL	NULL	NULL	3	1/2/2007	...
9942	80113	NULL	4	1/2/2007	...

Alternative SQL, for SQL Server 2005

DECLARE @housingrequest_roommates_numbered TABLE
(
	num int,
	Request int,
	Roommate int
)

-- build a temp table, using row_number() to number rows for each request
insert into @housingrequest_roommates_numbered
select row_number() OVER (PARTITION BY Request ORDER BY Request) as num, Request, Roommates
from housingrequest_roommates

-- then select from it.  If your query isn't too complex, you can probably combine this query with the above select and not use a temp table
select
(SELECT Roommate from @housingrequest_roommates_numbered
		WHERE Request = r.id
		and num = 1)
	as roommate1,
(SELECT Roommate from @housingrequest_roommates_numbered
		WHERE Request = r.id
		and num = 2)
	as roommate2,
(SELECT Roommate from @housingrequest_roommates_numbered
		WHERE Request = r.id
		and num = 3)
	as roommate3,
/* more columns */
*
from housingrequest r