Flat file to QUASI-Relational ?

[meyvn77@yahoo.com]

I have a flat file table that describes crash data in SQL Server.
It contains vehicle information.

I would like to know if anyone knows a SQL statement that could go from
this
table= events

CRASHID | VEH1_TYPE | VEH2_TYPE | VEH_3TYPE
-------------------------------------------
555555 | CAR | TRUCK | VAN

TO

CRASHID | VEH_TYPE | VEH_NUMBER
--------------------------------
555555 CAR 1
555555 Truck 2
555555 VAN 3


Any Ideas? I am relitively new at this and can only see how it could be
done by creating multiple tables and appending them.
Any help that could create the end selection in one query would be
great.

Thanks,
Chuck

[David Portas]

Re: Flat file to QUASI-Relational ?

INSERT INTO NewTable (crashid, veh_type, veh_number)
SELECT crashid, veh1_type, 1
FROM OldTable
WHERE veh1_type IS NOT NULL
UNION ALL
SELECT crashid, veh2_type, 2
FROM OldTable
WHERE veh2_type IS NOT NULL
UNION ALL
SELECT crashid, veh3_type, 3
FROM OldTable
WHERE veh3_type IS NOT NULL ;

--
David Portas
SQL Server MVP
--

[meyvn77@yahoo.com]

Re: Flat file to QUASI-Relational ?

Great.. Just what I was lookin for... Thanks for the help it is really
nice to have ppl who can/will help out..