> I have two tables. Table1 contains two fields. Field1 contains names
> of job types (Accountant,Doctor,etc.) Field2 contains the number of
[quoted text clipped - 15 lines]
>
> I am using Sql Server 2000.
There is a good advice that you for this type of questions include
CREATE TABLE statements for your tables, INSERT statements with sample
data, and the desired output given the sample. This can help to clarify
your question. Below I have such a script, that should address your
problem as I understood it, but in case I misunderstood, it will be
less useful. I could not resist including a solution for SQL 2005, since
this is a single query, whereas SQL 2005 requires a temp table.
CREATE TABLE jobs (jobtype varchar(20) NOT NULL PRIMARY KEY,
cnt int NOT NULL)
CREATE TABLE actions (jobtype varchar(20) NOT NULL,
unit varchar(20) NOT NULL,
action varchar(20) NOT NULL,
PRIMARY KEY (jobtype, unit, action))
INSERT jobs (jobtype, cnt) VALUES ('Dentist', 2)
INSERT jobs (jobtype, cnt) VALUES ('Plumber', 1)
INSERT actions (jobtype, unit, action)
VALUES ('Dentist', 'A', 'Audit')
INSERT actions (jobtype, unit, action)
VALUES ('Dentist', 'A', 'Test')
INSERT actions (jobtype, unit, action)
VALUES ('Dentist', 'A', 'Visit')
INSERT actions (jobtype, unit, action)
VALUES ('Dentist', 'A', 'Retire')
INSERT actions (jobtype, unit, action)
VALUES ('Dentist', 'B', 'Audit')
INSERT actions (jobtype, unit, action)
VALUES ('Dentist', 'B', 'Test')
INSERT actions (jobtype, unit, action)
VALUES ('Dentist', 'B', 'Visit')
INSERT actions (jobtype, unit, action)
VALUES ('Dentist', 'B', 'Retire')
INSERT actions (jobtype, unit, action)
VALUES ('Plumber', 'A', 'Visit')
INSERT actions (jobtype, unit, action)
VALUES ('Plumber', 'A', 'Retire')
INSERT actions (jobtype, unit, action)
VALUES ('Plumber', 'B', 'Audit')
INSERT actions (jobtype, unit, action)
VALUES ('Plumber', 'B', 'Test')
go
-- SQL 2005
SELECT a.jobtype, a.unit, a.action
FROM (SELECT DISTINCT j.jobtype, j.cnt, a.unit
FROM jobs j
JOIN actions a ON a.jobtype = a.jobtype) AS j
CROSS APPLY (SELECT TOP(j.cnt) a.jobtype, a.unit, a.action
FROM actions a
WHERE a.jobtype = j.jobtype
AND a.unit = j.unit
ORDER BY newid()) AS a
ORDER BY a.jobtype, a.unit, a.action
go
-- SQL 2000
CREATE TABLE #temp (ident int IDENTITY,
jobtype varchar(20) NOT NULL,
unit varchar(20) NOT NULL,
action varchar(20) NOT NULL,
PRIMARY KEY (jobtype, unit, action))
INSERT #temp (jobtype, unit, action)
SELECT jobtype, unit, action
FROM actions
ORDER BY jobtype, unit, newid()
SELECT t.jobtype, t.unit, t.action
FROM #temp t
JOIN (SELECT jobtype, unit, minident = MIN(ident)
FROM #temp
GROUP BY jobtype, unit) AS t2 ON t.jobtype = t2.jobtype
AND t.unit = t2.unit
JOIN jobs j ON j.jobtype = t.jobtype
WHERE t.ident BETWEEN t2.minident AND t2.minident + j.cnt - 1
ORDER BY t.jobtype, t.unit, t.action
go
DROP TABLE jobs
DROP TABLE actions
DROP TABLE #temp

Signature
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx