SQL Matrix

From splike.com

Jump to: navigation, search

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
iddate...
11/1/2007...
21/1/2007...
31/2/2007...
41/2/2007...
housingrequest_roommates
RequestRoommates
135235
288482
2120
24472
49942
480113


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:

roommate1roommate2roommate3iddate...
35235NULLNULL11/1/2007...
12044728848221/1/2007...
NULLNULLNULL31/2/2007...
994280113NULL41/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
Personal tools