SQL Server Performance

Creating a group of items from one column

Discussion in 'ALL SQL SERVER QUESTIONS' started by Tomson, May 6, 2012.

  1. Tomson New Member

    Hello every one...
    i have a students table , and i want to create a query that shows the possible groups of 3 students that i can create
    and i don't want to repeat the same names in different order
    for example
    i have this table

    StudentName
    Mike
    Sam
    Ron
    Jenny

    i want this Output
    column1 ----- column2----- column3
    Mike ------------ Sam ------------ Ron
    Mike ------------ Sam ------------ Jenny
    Mike ------------ Ron ------------ Jenny
    Sam ------------ Ron ------------ Jenny
  2. FrankKalis Moderator

    Welcome to the forum!
    If you can change the table definition a little bit, it's quite easy:

    Code:
    DECLARE @t TABLE (id int IDENTITY, student varchar(20))
    INSERT INTO @t (student) VALUES ('Mike'), ('Ron'), ('Sam'), ('Jenny')
    
    SELECT
        T1.student,
        T2.student,
        T3.student
    FROM
        @t T1
        JOIN
        @t T2 ON T1.id < T2.id
        JOIN
        @t T3 ON T1.id < T3.id AND T2.id < T3.id
    ORDER BY
        1;
    
    student              student              student
    -------------------- -------------------- --------------------
    Mike                Ron                  Sam
    Mike                Ron                  Jenny
    Mike                Sam                  Jenny
    Ron                  Sam                  Jenny
    
    (4 row(s) affected)
    
    

Share This Page