; with cte as

(

Select

row_number() over (partition by id order by id ) as rnum

,*

from staging

)

Select Id,name,max(empid) as empid,max(company_id) as company_id,addres

from cte

group by Id,case when rnum%2=0 then rnum else rnum+1...

CTE Example

CREATE TABLE #Sample(

Parent_ID char(1),

Child_ID char(1),

[Level] int

);

INSERT INTO #Sample(Parent_ID, Child_ID)

VALUES

('A', 'B'),

...

create table table_1

(

col1 varchar(100)

)

insert into table_1

values

('EQ181'),

('TX 116-3860')

Select

col1

,SUBSTRING(col1,1,Minimun-1) as col1

,SUBSTRING(col1,Minimun,data-Minimun+1)

from

(

select col1,min(N) as Minimun,DATALENGTH(col1) as data from table_1 cross apply Tally

where SUBSTRING(col1,n,1) like '%[0-9]%'

group by col1

) as a

You may...

Hope this helps

CREATE TABLE LotData (

Lot INT,

TranDate DATETIME,

Qty INT,

UOM CHAR(2),

RunBal INT );

GO

INSERT INTO LotData(Lot, TranDate, Qty, UOM, RunBal)

VALUES (114044, '5/27/2015 3:25 PM', 13, 'LB', 13)

, (114044, '6/12/2015 1:25 PM',...

Learned something new today 🙂

you can use Fn_split () function . The Code for it is available in google