SQL Query help to add addional column

Question

Hi All,

I need sql query help. i am selecting data in following way from my tables.

Id Name Accountno country Active insertdate
1 Chris 13456 Us 1 23/4/1995
1 Chris 6784 Us 0 24/5/1996

i want to disply above 2 records in to single record and add account no column as additional column like previous account no. like following out put expected

Id Name Accountno PreviousAccountno country Active insertdate
1 Chris 13456 6784 Us 1 23/4/1995

How achieve these .. like i have 8 lacks data and need to show these ?

Answer 1

you can use cross join and over functions combination very simple.
note:i use GuoxiongYuan-7218 data

DECLARE @T TABLE (  
     Id int,  
     Name varchar(50),  
     Accountno int,  
     Country varchar(50),   
     Active bit,   
     Insertdate date  
 );  
      
 INSERT INTO @T VALUES   
 (1, 'Chris', 13456, 'Us', 1, '1995-04-23'),  
 (1, 'Chris', 6784, 'Us', 0, '1996-05-24'), (1, 'Chris', 6785, 'Us', 0, '1996-05-24');  
      
  
 select * from @T x  
 select Id,Name,Accountno,c PreviousAccountNo,Country,Insertdate from(  
 select *,ROW_NUMBER() over (partition by Id,Name order by c ) rn from(  
 select t.*,y.Accountno as c from @T  t cross join  @T y  
 where t.Active=1 and y.Active=0  
 )x  
 )y where rn=1  
  
  
(3 rows affected)  
Id          Name                                               Accountno   Country                                            Active Insertdate  
----------- -------------------------------------------------- ----------- -------------------------------------------------- ------ ----------  
1           Chris                                              13456       Us                                                 1      1995-04-23  
1           Chris                                              6784        Us                                                 0      1996-05-24  
1           Chris                                              6785        Us                                                 0      1996-05-24  
  
(3 rows affected)  
  
Id          Name                                               Accountno   PreviousAccountNo Country                                            Insertdate  
----------- -------------------------------------------------- ----------- ----------------- -------------------------------------------------- ----------  
1           Chris                                              13456       6784              Us                                                 1995-04-23  
  
(1 row affected)  

Answer 2

Try this:

DECLARE @T TABLE (  
	Id int,  
	Name varchar(50),  
	Accountno int,  
	Country varchar(50),   
	Active bit,   
	Insertdate date  
);  
  
INSERT INTO @T VALUES   
(1, 'Chris', 13456, 'Us', 1, '1995-04-23'),  
(1, 'Chris', 6784, 'Us', 0, '1996-05-24');  
  
;WITH CTE_Active AS (  
	SELECT *  
	FROM @T  
	WHERE Active = 1  
),  
CTE_Inactive AS (  
	SELECT *  
	FROM @T  
	WHERE Active = 0  
)  
  
SELECT a.Id, a.Name, a.Accountno, i.Accountno AS PreviousAccountno, a.Country, a.Insertdate  
FROM CTE_Active AS a  
JOIN CTE_Inactive AS i ON a.Id = i.Id;  

Output:

Answer 3

I am little in a hurry and I don't have that much experience with those things...

I found a solution which might work for you:

SELECT [id]
,[name]
,[accountnumber]
,(select top 1 lead([accountnumber]) over (order by insertdate) from [dbo].[871768] ) as Previous
,[country]
,[active]
, insertdate
FROM [dbo].[871768] a
Where active = 1

This is my test-table

CREATE TABLE [dbo].871768 ON [PRIMARY]

I think there might be a better statement... but this is quick&dirty ;-)

Answer 4

Check LAG and LEAD functions in Help. If you always have just 2 rows, one active, one inactive, then you can also try

select a.Id, a.Name, a.AccountNo, i.AccountNo as PreviousAccountNo, a.Country, a.Active, a.InsertDate
from DataTable a
OUTER APPLY (select top (1) * from DataTable i where i.Id = a.Id -- assuming Id is enough
and i.Active = 0 order by i.InsertDate DESC -- last inactive row) I
where a.Active = 1 -- active row