Hi all, I have a table (Table1)

[kenjoswe]

Hi all,

I have a table (Table1) that looks like:

cnt Type
---- ----
1 5
2 5
3 5
4 6
5 5
6 6
7 5
8 6

Record 4 is type 6 and is directly after record 3 and should be on the same row as record 3.
...and so on

Based on Table1 I would like to create a new table:

cnt1 Type1 cnt2 Type2
----- ---- ---- -----
1 5
2 5
3 5 4 6
5 5 6 6
7 5 8 6

How can I do this with SQL-syntax?

/Kent J.

 

[Hubajube]

Will the cnt field always be integers increasing like that with no numbers skipping? Will Type always be 5 or 6? If not, what other values might they have?

 

[pascalsql]

Can you have many pairs of columns, such as
cnt1 Type1 cnt2 Type2 cnt1 Type1 cnt2 Type2 ...
----- ---- ---- ----- ----- ---- ---- -----
1 5
2 5
3 5 4 6 5 7 6 8
7 5 8 6
...

What is your goal? It seems your design is quite bizarre.

 

[kenjoswe]

OK!
My example wasn't so good so I will try another one.
What I have at hand is a single textfile with two different fileformat in it.

The file layout looks like:
Type,CustomerId,Amount,Product,Date
Type,CustomerId,Amount,Product,Date
Type,CustomerId,Amount,Product,Date
Type,AddOnService,Amount
Type,CustomerId,Amount,Product,Date

As you can see the fourth row has a different layout than the other and can be identified by a different Type value. The fourth record should be connected to the third row because that row or order has the propertie 'AddOnService'

If I put some data in the above fileformat:
5,1,100,A,123,A
5,2,80,A,123,B
5,3,120,A,123,C
6,Ab1,15


...and I want to accomplish:
CustomerId Amount Product Date AddOnService Amount
1 100 A 123
2 80 B 123
3 120 C 123 Ab1 15

Hope this will clarify what I'm after.

/Kent J.

 

[pascalsql]

Solution 1: Create a package in DTS to handle the changing layout.

Solution 2:
Create a temp table to import your data with an order_id identity field. This field is important to keep track of order as it is the only way to link type 6 rows with their corresponding type 5 rows.
You can use bulkinsert instruction (see in BOL)

create table #T
( order_id identity,
type_id tinyint,
import_col1 varchar(30) null,
import_col2 varchar(30) null,
import_col3 varchar(30) null,
import_col4 varchar(30) null
)

When you have this table, you can select in your fixed table all records of type 5 and left join with a subquery for type 6 rows with order_id = order_id_of_type_5 + 1

I don't have sql server to check the query. I should look like:

select
T5.import_col1 as customerid, -- add type conversion
T5.import_col2 as amount, -- add type conversion
T5.import_col3 as product, -- add type conversion

T6.import_col1 as addservice, -- add type conversion
T6.import_col2 as addamount -- add type conversion
from #T T5
left join #T T6 on T6.order_id = T5.order_id+1 and T6.type_id = 6
where type_id = 5

When using outer joins, triple check with different small samples that you REALLY get what you want. It's too easy to get irrelevant resultsets that are impossible to check with large data.