June 13, 2011 at 3:48 am
There is a table Students with structure as follows:
Sid SName TeamId
1 S1 1
2 S2 2
3 S3 3
4 S4 1
5 S5 2
6 S6 3
One team can only have 2 students as shown in the data above. I want to write a query which will return the teamid and the id/name of 2 students which belong to that team, as shown below.
TeamId Student1 Student2
1 1 4
2 2 5
3 3 6
What will be the query for this?
June 13, 2011 at 3:56 am
I think you need to use the PIVOT statement (or you can achieve the same with a series of CASE expressions). Have a read about that and post back if there's anything you don't understand.
John
June 13, 2011 at 7:49 am
There are many solutions. Here's a fun one:
Since you have specified that you can only have two students (and with the reasonable assumption that each student ID is unique to a student), you can just use min() and max() to get the two student ID's.
Since you need the names as well, though, attempting to use min and max results in a bit of an ugly set of subqueries. But min() and max() are just the top 1 ordering in opposite directions, aye?
You could therefore use cross apply. You would be cross applying twice, each time getting the data in the top 1 row, but ordering by student ID in opposite directions. Do this for each distinct team.
June 13, 2011 at 4:43 pm
Maybe something along these lines will work:
WITH cte(StudentId, StudentName, TeamId)
AS (
SELECT CAST (1 AS INT),CAST ('S1' AS CHAR(2)),CAST (1 AS INT)
UNION SELECT 2,'S2',2
UNION SELECT 3,'S3',3
UNION SELECT 4,'S4',1
UNION SELECT 5,'S5',2
UNION SELECT 6,'S6',3
)
SELECT TeamId,
MIN(StudentId),
MAX(StudentId)
FROM cte
GROUP BY TeamId ;
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 14, 2011 at 6:14 pm
CELKO (6/14/2011)
>> One team can only have 2 students as shown in the data above. I want to write a query which will return the team_nbr and the id/name of 2 students which belong to that team, as shown below. <<Enforce your business rules in the DDL; this will let you have three teams with at most two members:
CREATE TABLE Student_Teams
(student_id INTEGER NOT NULL PRIMARY KEY,
student_name VARCHAR(25) NOT NULL,
team_nbr INTEGER NOT NULL
CHECK (team_nbr IN (1,2,3)),
member_nbr INTEGER NOT NULL
CHECK (team_nbr IN (1,2)),
UNIQUE (team_nbr, member_nbr)
);
Seriously, your recommendation is to switch to a hardcoded IN value for what's obviously a sample data set? :blink:
The code that OPC offered will get your solution in this case. Otherwise you're looking at a PIVOT of the data, but that would require an additional field in the original table to pivot on. You will probably want that additional field anyway to help control for mistakes in adding to teams.
A unique index across teamID and MemberNumber (pick your term, it goes 1,2,3... within each team) and then a constraint making sure that MemberNumber isn't higher than 2 (in this case, you could associate this with a project or study which suddenly gets a lot more complex).
Once you have that, building out the Pivot is easy.
That said, this is usually something you let the reporting/display layer do, as it's a data pivot which is a display issue.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
June 14, 2011 at 6:40 pm
CELKO (6/14/2011)
... avoid needless dialect ...
I'm very much in favor of that idea. You should try it sometime.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply