sql server 2012 - Transpose Rows to columns from multiple tables without summary

Question

I need to pull information in from multiple different tables with the common fields of nar_num and seq_num, but each data set has a different accountid.

What I am trying to do is find where the nar_num and seq_num are identical in both data sets and then assign an accountid that is either the accountid from the first data set or a new number.

For example: My first data set has

accountid  nar_num  seq_num
1009       68428      1
1009       68429      2
1044       50005      1
1045      211981      1
1046      244155      1

my second data set has:

accountid   nar_num seq_num
12486      68428      1
12486      68429      2
12487      50005      1
12488     211981      1
12489     244155      1
12489      5005       2

what I ultimately want it to look like is this:

accountid   seq_num1    seq_num2
1009         68428        68429
1004         5005   
1045         211981 
1046         244155 
98001        241155       5005

where the sequence numbers and nar_nums match from source 1 and 2 then keep the accountid from source 1, where they dont assign a new accountid starting at a specified number and incrementing as more records are added.

My current scipt looks like this:

--misc debtors
with CTE_debtor_list (accountid,nar_num, seq_num)
as
(Select distinct
    ab.deb_num as accountid
    ,ar.nar_no1 as nar_num
    ,1 as seq_num
    from auarmast ar  
    inner join auarbals ab on ar.deb_num = ab.deb_num and ab.stu_cde = 1 and not(ab.ctg_cde in (17,99))
    inner join aunrmast nr on ar.nar_no1 = nr.nar_num 
    where ab.stu_cde = 1 and not(ab.ctg_cde in (7,99))
UNION
Select distinct
    ab.deb_num as accountid
    ,ar.nar_no2 as nar_num
    ,2 as seq_num
    from auarmast ar  
    inner join auarbals ab on ar.deb_num = ab.deb_num and ab.stu_cde = 1 and not(ab.ctg_cde in (17,99))
    inner join aunrmast nr on ar.nar_no2 = nr.nar_num 
    where ab.stu_cde = 1 and not(ab.ctg_cde in (7,99))
UNION
Select distinct
    ab.deb_num as accountid
    ,ar.nar_no3 as nar_num
    ,3 as seq_num
    from auarmast ar  
    inner join auarbals ab on ar.deb_num = ab.deb_num and ab.stu_cde = 1 and not(ab.ctg_cde in (17,99))
    inner join aunrmast nr on ar.nar_no3 = nr.nar_num 
    where ab.stu_cde = 1 and not(ab.ctg_cde in (7,99))
UNION
Select distinct
    ab.deb_num as accountid
    ,ar.nar_no4 as nar_num
    ,4 as seq_num
    from auarmast ar  
    inner join auarbals ab on ar.deb_num = ab.deb_num and ab.stu_cde = 1 and not(ab.ctg_cde in (17,99))
    inner join aunrmast nr on ar.nar_no4 = nr.nar_num 
    where ab.stu_cde = 1 and not(ab.ctg_cde in (7,99))
UNION
    select ass_num as accountid, nar_num, 
    seq_num = ROW_NUMBER() OVER(PARTITION BY aurtownr.ass_num ORDER BY aurtownr.ass_num)
    from aurtownr)

I will be adding additional data sources.

Currently I have up to seq_num = 17 but this is a moving target based on data from one data set

Jointables

question from:https://stackoverflow.com/questions/65912828/transpose-rows-to-columns-from-multiple-tables-without-summary

Answer 1

Waitting for answers