Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2009
    Posts
    12

    Unanswered: sequence generation - Matrix

    I am using sqlserver 2005. Table Details( sequence,person, Job) are available. if 'N' persons and 'M' Jobs are there how to generate sequence in matrix.for 2 persons and 2 jobs then in Details table data sequence should be for Sequence, Person, Job are 1 1 1, 1 1 2, 2 2 1, 2 2 2, 3 1 1, 3 2 2 , 4 1 2 , 4 2 1..

    in Explination: sequence 1: first Person is alloted 1 and 2(for 1st Person 2 possible chances of allocating jobs only and 2nd Person have no jobs here).

    Sequence 2: for 2nd Person 2 and 1 are allocated(for 1st person no possibilites of job and 2nd person has two chances of jobs).

    Sequence 3: for 1st person first job and 2nd person second job.

    Sequence 4: for 1st person second job and 2nd person first job. like wise.

    Plz help how to generate this in sp if N and M are passed as Input parameters for NxM matrix. Thanks in Advance.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    If you are using SQL Server then why did you post in the Adabas forum?!

    Post moved.
    George
    Home | Blog

  3. #3
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    What you want is all the possible combinations of Persons with Jobs. That is called a Cartesian product. You can get it by doing a CROSS JOIN between those two tables.
    Code:
    CREATE TABLE Persons (
    PersonId  INTEGER NOT NULL,
    CONSTRAINT pk_Persons PRIMARY KEY (PersonId)
    )
    
    CREATE TABLE Jobs (
    JobId  INTEGER NOT NULL,
    CONSTRAINT pk_Jobs PRIMARY KEY (JobId)
    )
    
    INSERT INTO Persons (PersonId) VALUES (1)
    INSERT INTO Persons (PersonId) VALUES (2)
    
    INSERT INTO Jobs (JobId) VALUES (1)
    INSERT INTO Jobs (JobId) VALUES (2)
    
    SELECT PersonId, JobId
    FROM Persons 
       CROSS JOIN Jobs
    For the values in the sequence column: if you would INSERT the above result set in a table with an Identity column, SQL Server will generate the sequence for you. Otherwise you can use ROW_NUMBER().
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •