rSQL While Loop insert

Question

*Updated - Please see below(Past the picture)

I am really stuck with this particular problem, I have two tables, Projects and Project Allocations, they are joined by the Project ID.

My goal is to populate a modified projects table's columns using the rows of the project allocations table. I've included an image below to illustrate what I'm trying to achieve.

A project can have up to 6 Project Allocations. Each Project Allocation has an Auto increment ID (Allocation ID) but I can't use this ID in a sub-selects because it isn't in a range of 1-6 so I can distinguish between who is the first PA2 and who is PA3.

Example:

(SELECT pa1.name FROM table where project.projectid = project_allocations.projectid and JVID = '1') as [PA1 Name],

(SELECT pa2.name FROM table where project.projectid = project_allocations.projectid and JVID = '1') as [PA2 Name],

The modified Projects table has columns for PA1, PA2, PA3. I need to populate these columns based on the project allocations table. So the first record in the database FOR EACH project will be PA1.

I've put together an SQL Agent job that drops and re-creates this table with the added columns so this is more about writing the project allocation row's into the modified projects table by row_num?

Any advice?

--Update

What I need to do now is to get the row_number added as a column for EACH project in order of DESC.

So the first row for each project ID will be 1 and for each row after that will be 2,3,4,5,6.

I've found the following code on this website:

use db_name
with cte as
       (
            select *
           , new_row_id=ROW_NUMBER() OVER (ORDER BY eraprojectid desc)
          from era_project_allocations_m
         where era_project_allocations_m.eraprojectid = era_project_allocations_m.eraprojectid
       )
update cte
set row_id = new_row_id

update cte
set row_id = new_row_id

I've added row_id as a column in the previous SQL Agent step and this code and it runs but it doesn't produce me a row_number FOR EACH projectid.

As you can see from the above image; I need to have 1-2 FOR Each project ID - effectively giving me thousands of 1s, 2s, 3s, 4s.

That way I can sort them into columns :)

Answer 1

From what I can tell a query using row number is what you are after. (Also, it might be a pivot table..)

Example:

create table Something (
  someId int,
  someValue varchar(255)
  );

insert into Something values (1, 'one'), (1, 'two'), (1, 'three'), (1, 'four'), (2, 'ein'), (2, 'swei'), (3, 'un')

with cte as (
select someId,
       someValue,
       row_number() over(partition by someId order by someId)  as rn
from Something
)
select distinct someId,
       (select someValue from cte where ct.someId = someId and rn = 1) as value1,
       (select someValue from cte where ct.someId = someId and rn = 2) as value2,
       (select someValue from cte where ct.someId = someId and rn = 3) as value3,
       (select someValue from cte where ct.someId = someId and rn = 4) as value4
into somethingElse
from cte ct;

select * from somethingElse;

Result:

someId  value1  value2  value3  value4
1       one     two     three   four
2       ein     swei    NULL    NULL
3       un      NULL    NULL    NULL