sequence generation - Matrix

phaniprasanth · #1 (**permalink**) 09-14-09, 09:41

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.

gvee · #2 (**permalink**) 09-14-09, 10:23

If you are using SQL Server then why did you post in the Adabas forum?!

Post moved.

Wim · #3 (**permalink**) 09-14-09, 13:41

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().

Internet Services

Web Development

Digital Marketing

Consumer Tech