SQL Matrix
From splike.com
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