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 :)

Better than an picture was to provide some sample data and expected output. And please re-tag your question with the appropriate version of your SQL-Server. — Shnugo, Jul 15 '16 at 15:49
Here is a great place to start. http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/ And FWIW I doubt you need a loop to insert data. — Sean Lange, Jul 15 '16 at 15:55
@SeanLange I'm not sure about that, The table won't get updated itself; It i will be used to create DB views for a front-end so an SQL Job will run at night to get the data up to date. — Justin, Jul 18 '16 at 09:30
How do you decide what is PA1, 2, 3, etc? Is it the row numberr in ProjectAllocations? — Tobb, Jul 18 '16 at 09:31
It is decided by the Row number, the first record will be PA1 then all rows after PA1 will be 2,3,4 - As they are inserted into the DB table from my front end — Justin, Jul 18 '16 at 09:42
You need to partition,`new_row_id=ROW_NUMBER() OVER (PARTITION BY eraprojectid ORDER BY eraprojectid desc)`. This way you will get one sequence per `eraprojectid`. See my answer. — Tobb, Jul 18 '16 at 10:52

Tobb · Accepted Answer · 2016-07-18T09:45:46.603

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

Yes indeed, Row number is what I'm looking for. I need to add the Row_Number as a non-null; non-unique field that is populated after the initial rows are created. — Justin, Jul 18 '16 at 09:43
For right now, What I'm trying to do is simply get a row_number column added and populated FOR Each ProjectID - That way I can see sub-selects to bring back what I need. I'll update the main question shortly with more info. — Justin, Jul 18 '16 at 10:03

rSQL While Loop insert

1 Answers1