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
    Tomson, May 6, 2012
    #1

  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)
    FrankKalis, May 7, 2012
    #2

Share This Page