Re: Create one record of Multi record

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(
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):

TAG_NUMBER_1 TAG_STATE_1 TAG_NUMBER_2 TAG_STATE_2 ------------ ----------- ------------ -----------

123456       IL          234567       IL      
3456TT       WI      

Using ROW_NUMBER(), this can be done very easily:

WITH VEHICLE_ACCIDENT_RN(ACCIDENT_ID, ROWNUM, TAG_NUMBER, TAG_STATE) AS
(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 - 15:11:40 CST