Multiple formats in Input table 1

[schusser]

I am reading a table that has multiple formats in it. It was imported as a text file which originated on our mainframe as a report file and I need to extract data from it to be used in SQL Server.

The data looks like this:

row1: dept 1 tran 020
row2: dept 1 tran 020
row3: totaltrans 001245

I need to get a single row that looks like:

row: dept 1 tran 020 total 001245

I've been able to narrow it down to row1 and row3, but don't know how to combine them into a single row since there is no common field value to match on.

Thanks in advance for your help.

 

[amrita418]

Are Row1 and Row2 the same always? What identifies Row3 to be identified with Row1 instead of say

row7: Dept 4 tran 040?

Regards,
AA

 

[schusser]

Hi AA,

Row1 and Row2 (detail lines of a report) will always have the same dept value, but may have different tran codes. Unfortunately there is nothing in Row3 (the subtotal line of a report)that I can key on. The Dept is not carried there. And the transaction counts are not carried on the detail lines. They are on a separate line with no corresponding information.

I'm literally working from a report that I've been able to import as text into a table. I then identify positionally the fields that I need.

Thanks in advance for any help.

 

[amrita418]

Adding an identity column should do the trick.

select       a.col1,
             a.col2,
             b.col2
from 		#TableA a,
            (select 	col2,
                        iden
             from       #tableA
             where 	 col1 = 'totaltrans') b
where 	   col1 != 'totaltrans'
	         and b.iden = a.iden + 1

Regards,
AA

 

[amrita418]

Forgot to paste the sample data.

create table #TableA(col1 varchar(10), col2 varchar(10), Iden int identity(1, 1))

insert into #TableA values ('dept 1', 'tran 020')
insert into #TableA values ('dept 1', 'tran 020')
insert into #TableA values ('totaltrans', '001245')
insert into #TableA values ('dept 2', 'tran 020')
insert into #TableA values ('dept 2', 'tran 020')
insert into #TableA values ('totaltrans', '001246')

Regards,
AA

 

[schusser]

AA,

This worked great! Tweaked it a little for my real data and now it's doing exactly what I needed.

Thanks!