Re: Create one record of Multi record

ak_tiredofspam_at_yahoo.com (AK) wrote in message news:<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(
> 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.

Hi

Thanks for my help. could you please explain more. I don't know how this part works.

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) Thanks Received on Sun Mar 28 2004 - 18:57:31 CEST