Rows in Columns

Question

I have this table:

Id Kind 1 MODEL 1 MOTOR 2 MODEL 2 MOTOR 3 MOTOR 4 MODEL 

And I want to insert into the anothe table:

 IdModel IdMotor 1 1 1 2 1 3 2 1 2 2 2 3 4 1 4 2 4 3 

I know how to do this with cursors, but it is really very slow. I tried with the union, but it looks like today is not my best day!

I also know that this can be done in SQL 2005 using pivot, but I have to do this with SQL Server 2000.

Any Transact-SQL gurus out there with a good and quick query?

Thanks in advance!

Answer 1

This seems to work:

 INSERT Table2 SELECT model.id, motor.id FROM Table model, Table motor WHERE model.Kind = 'MODEL' and motor.Kind = 'MOTOR' 

Answer 2

 INSERT INTO AnotherTable SELECT [IdModel] , [IdMotor] FROM ( SELECT [IdModel] = ID FROM ATable WHERE Kind = 'MODEL' ) md CROSS APPLY ( SELECT [IdMotor] = ID FROM ATable WHERE Kind = 'MOTOR' ) mt