Re: Create one record of Multi record
Date: 27 Mar 2004 13:11:40 -0800
Message-ID: <46e627da.0403271311.6465e113_at_posting.google.com>
very similar to your problem:
Laying out two records on a line using a sequence table
Given this table structure and data:
CREATE TABLE VEHICLE_ACCIDENT(
TAG_NUMBER_1 TAG_STATE_1 TAG_NUMBER_2 TAG_STATE_2
------------ ----------- ------------ -----------
Using ROW_NUMBER(), this can be done very easily:
WITH VEHICLE_ACCIDENT_RN(ACCIDENT_ID, ROWNUM, TAG_NUMBER, TAG_STATE)
AS
ACCIDENT_ID INT NOT NULL,
TAG_NUMBER CHAR(10) ,
TAG_STATE CHAR(2) );
INSERT INTO VEHICLE_ACCIDENT
VALUES(1,'123456','IL'),(1,'234567','IL'),(1,'34567TT','WI');
(other columns omitted to keep things simple). Note that there may be
more than 2 vehicles involved in an accident. There is a requirement
to lay out two records on one line, like this (when three vehicles
were involved):
123456 IL 234567 IL
3456TT WI
(SELECT ACCIDENT_ID, ROW_NUMBER() OVER() AS ROWNUM, TAG_NUMBER,
TAG_STATE
FROM VEHICLE_ACCIDENT)
SELECT
LEFT_SIDE.TAG_NUMBER AS TAG_NUMBER_1,
LEFT_SIDE.TAG_STATE AS TAG_STATE_1,
RIGHT_SIDE.TAG_NUMBER AS TAG_NUMBER_2,
RIGHT_SIDE.TAG_STATE AS TAG_STATE_2
FROM
(SELECT L.*, (L.ROWNUM+1)/2 AS PAGENUM FROM VEHICLE_ACCIDENT_RN L
WHERE MOD(ROWNUM,2)=1)AS LEFT_SIDE
LEFT OUTER JOIN
(SELECT R.*, (R.ROWNUM+1)/2 AS PAGENUM FROM VEHICLE_ACCIDENT_RN R
WHERE MOD(ROWNUM,2)=0)AS RIGHT_SIDE
ON LEFT_SIDE.PAGENUM = RIGHT_SIDE.PAGENUM
WHERE LEFT_SIDE.ACCIDENT_ID=1 AND (RIGHT_SIDE.ACCIDENT_ID=1 OR
RIGHT_SIDE.ACCIDENT_ID IS NULL)
This query gives correct results for both odd and even number of
involved vehicles. Feel free to add records and verify it. Again, as
in the previous chapters, there are several other ways to solve this
problem. Using ROW_NUMBER() allows for a solution that is very simple,
quick to develop and easy to understand.
Received on Sat Mar 27 2004 - 22:11:40 CET