?? on While loop go thru each record X number of times

• jbalbo

  SSCertifiable

  Points: 7580

  More actions

  April 24, 2018 at 10:08 am

  #410454

  Hi

  Trying to setup a while loop to go thru each record on a temp table for 3 different rooms  and do some case stuff each time .

  So I have a temp table with some data say StudentName
  student1
  student2

  I want to assign each room (three rooms)  to each student 

  so the end data would look similar to 

  room1 student1
  room2 student1 
  room3 student1 
  room1 student2
  room2 student2 
  room3 student2

  Any ideas would be great

  Thanks

• Eirikur Eiriksson

  SSC Guru

  Points: 182882

  More actions

  April 24, 2018 at 10:26 am

  #1988073

  jbalbo - Tuesday, April 24, 2018 10:08 AM

  Hi

  Trying to setup a while loop to go thru each record on a temp table for 3 different rooms  and do some case stuff each time .

  So I have a temp table with some data say StudentName
  student1
  student2

  I want to assign each room (three rooms)  to each student 

  so the end data would look similar to 

  room1 student1
  room2 student1 
  room3 student1 
  room1 student2
  room2 student2 
  room3 student2

  Any ideas would be great

  Thanks

  This is simply a cartesian product of the two sets, simply select from one and cross apply the other.
  😎

• sgmunson

  SSC Guru

  Points: 110639

  More actions

  April 24, 2018 at 10:28 am

  #1988074

  Why do you need a WHILE loop?
  WITH STUDENTS AS (

      SELECT 'Student1' AS StudentName UNION ALL
    SELECT 'Student2'
)
SELECT R.RoomName, S.StudentName
FROM STUDENTS AS S
    CROSS APPLY (VALUES ('Room1'), ('Room2'), ('Room3')) AS R (RoomName)
ORDER BY S.StudentName, R.RoomName;

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)

• ScottPletcher

  SSC Guru

  Points: 100948

  More actions

  April 24, 2018 at 11:09 am

  #1988084

  Standard CROSS JOIN:

  SELECT StudentName, RoomName
  FROM Students
  CROSS JOIN ( VALUES('room1'),('room2'),('room3') ) AS Rooms(RoomName)

  SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.