# Thread: sequence generation - Matrix

1. Registered User
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. www.gvee.co.uk
Join Date
Jan 2007
Location
UK
Posts
11,445
If you are using SQL Server then why did you post in the Adabas forum?!

Post moved.

3. Registered User
Join Date
Nov 2004
Posts
1,427
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().

#### Posting Permissions

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