Create temp row for each row in existing table.

Question

I have two tables. In the one table I have a list of dorm rooms with maximum occupancy like so:

dorm_building | dorm_room | max_occupancy

Then I have a list of students and the room they are assigned to like this:

people_id | people_name | dorm_building | dorm_room

I want to create a table that has a row for each potential occupant (e.g. if a room has five possible occupants, there should be five rows for this table). Then I want to join the student records to this table. I'll then spit this out using Reporting Services to provide a visual map of who is in what room and where empty spots are still available.

Help?

Answer 1

With Numbers As
    (
    Select Row_Number() Over ( Order By C1.object_id ) As Value
    From sys.syscolumns As C1
        Cross Join sys.syscolumns As C2
    )
    , NumberedStudents As
    (
    Select people_id, people_name, dorm_building, dorm_room
        , Row_Number() Over ( Partition By dorm_building, dorm_room Order By people_id ) As OccupantNum
    From Students
    )
Select ...
From DormRooms
    Join Numbers
        On N.Value <= DormRooms.max_occupancy
    Left Join NumberedStudents
        On NumberedStudents.dorm_building = DormRooms.dorm_building
            And NumberedStudents.dorm_room = DormRooms.dorm_room
            And NumberedStudents.OccupantNum= Numbers

I'm using two features in this solution which are available in SQL Server 2005 and beyond. The first is common-table expression or CTE for short and the second is a ranking function (in this case Row_Number). The common-table expressions are the Numbers table and the NumberedStudents table. Think of these as saved views or queries. The Numbers CTE creates a sequential list of numbers by cross joining any two tables (I chose sys.syscolumns). That enables me to generate the "placeholder" rows you requested. The Row_Number function simply creates a sequential list of numbers for every row returned.

In the NumberedStudents CTE, I am also using the Partition By feature with the Row_Number function which restarts the numbering of occupants for each dorm_building and dorm_room. This will effectively give a sequential number to each student in the room.

Answer 2

Do you actually need the temporary table? Why not just write a fairly simple query, something like:

select dr.dorm_building, dr.dorm_room, dr.max_occupancy, ra.people_id, ra.people_name
from dorm_room_table dr left join room_assignment_table ra 
on dr.dorm_building = ra.dorm_building and dr.dorm_room = ra.dorm_room

then use a table, grouped by dorm_room and dorm_building, with the details rows to report occupancy and a group footing to report vacant places (Fields!max_occupancy.value - CountRows()).