Convert row to columns in the same table and delete the row from the table

Question

how to select rows from a table by converting matching rows to columns within the same table and delete the row from the table. There are 4 Account number (100,200,111,222) where account number 100 is associated to 111 and account number 200 is associated to 222. Here each company account(200) is associated with sister company account(222).Please refer to below pic for original table and expected result. There could be rows which has no associated companies. End result should have if a company has associated rows they need to be converted to columns in one line and individual rows as well. I tried using Pivot but unable to achieve the result. Any suggestions could help

Answer 1

Hi @A Vidhya ,

Thank you so much for posting here in Microsoft Q&A.

After checking your original table, account number 100 should be associated to 222 instead of 111.

So please help double check the expected output provided. Thanks.

You could also refer below and check whether any of them is helpful to you.

create table mytable  
(  
Company varchar(20),  
Acc_No int,  
Amount decimal(8,2),  
Sister_Company varchar(20)  
)  
  
insert into mytable values  
('X1 Ltd',100,-2.39,'P1 Corp.'),  
('Z1 Ltd',111,576.23,'Z1-2 Ltd'),  
('P1 Corp.',222,2.39,'X1 Ltd'),  
('A1 Inc',200,-4589.24,'PR-2 Inc'),  
('PR-2 Inc',222,3356,'A1 Inc')  
  
select a.Company,a.Acc_No,a.Amount,a.Sister_Company,b.Amount Amount_1,b.Acc_No Acc_No_1  
 from mytable a   
left join  mytable b  
on a.Sister_Company=b.Company  
where a.Acc_No<b.Acc_No or b.Acc_No is null  

Or:

;with cte as (  
select case when a.Acc_No<b.Acc_No then a.Company+'#'+a.Sister_Company else b.Company+'#'+b.Sister_Company end combine,  
a.Company,a.Acc_No,a.Amount,a.Sister_Company,b.Amount Amount_1,b.Acc_No Acc_No_1  
 from mytable a   
left join  mytable b  
on a.Sister_Company=b.Company)  
,cte1 as (  
select Company,Acc_No,Amount,Sister_Company,Amount_1,Acc_No_1  
,ROW_NUMBER() OVER(PARTITION BY combine ORDER BY Acc_No) rn  
 from cte )  
select Company,Acc_No,Amount,Sister_Company,Amount_1,Acc_No_1  
 from cte1 where rn=1  

Output:

Company Acc_No Amount Sister_Company Amount_1 Acc_No_1  
X1 Ltd 100 -2.39 P1 Corp. 2.39 222  
Z1 Ltd 111 576.23 Z1-2 Ltd NULL NULL  
A1 Inc 200 -4589.24 PR-2 Inc 3356.00 222  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.