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 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 it with cursors, but it's indeed very slow. I've tried with union but it looks like today is not my best day!

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

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

Thanks in advance!

The table I want to create doesn't exist, so initially there is only 1 table. Yes, I want to create all possible combinations od IdModel and IdMotor. — Franklin Albricias, Jun 16 '11 at 11:09

score 3 · Accepted Answer · answered Jun 16 '11 at 10:59

3

Looks like this will work:

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

answered Jun 16 '11 at 10:59

Jon Egerton

40,401
11
97
129

Thank you! As I say before: I'm a little down today 8-[ – Franklin Albricias Jun 16 '11 at 11:20
Wow. +1 for partial psychic skills. This answer assumes a different model than the question but is apparently right. – Brock Adams Jun 19 '11 at 00:48

score 0 · Answer 2 · answered Jun 16 '11 at 11:00

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

Rows in columns

2 Answers2

Linked