Two rows into one

[Danu]

I have a table which has a record for each job number involved in the process.
Example

Process Job Number
111 215855
111 58642
111 6955566
234 8256

I need to move all the job numbers associated with a process into separate
fields on the same row as the process number in a new table.

Example
111 215855 58642 6955566
234 8256

Can I use a crosstab? WOuld I be able to continue running queries and
generating tables from the crosstab query? If not, any suggestions how to
achieve what I need?

Thank you in advance.

 

[Lord Kelvan]

for a cross tab you need three fields and crosstabs are for
summarising information not really for displaying it

sorry i have no ideas

 

[KARL DEWEY]

You can use this query for the third field --
SELECT Q.[Process], Q.[Job Number], (SELECT COUNT(*) FROM Danu Q1
WHERE Q1.[Process] = Q.[Process]
AND Q1.[Job Number] < Q.[Job Number])+1 AS Job_Number
FROM Danu AS Q
ORDER BY Q.[Process], Q.[Job Number];

BUT you must take it to a temporary table to use it in a crosstab query --
SELECT Q.Process, Q.[Job Number], (SELECT COUNT(*) FROM Danu Q1
WHERE Q1.[Process] = Q.[Process]
AND Q1.[Job Number] < Q.[Job Number])+1 AS Job_Number INTO
Temp_Process_List
FROM Danu AS Q
ORDER BY Q.Process, Q.[Job Number];