Re: How can you create a flatten table in SQL

This will do it, but it becomes impractical if there are more than a few rows. Your example did not say what you were trying to accomplish, or what criteria you would use to create a new column in the result set. This uses a group by subquery to get rid of duplicate triplets and order the data, then simply decodes each row into its appropriate column based on its rownum value. This could also be done with a UNION query, but this seemed simpler without knowing more about what you are trying to do. I work with databases that have hundreds of thousands to millions of rows, so I would never try to make a result set with one column per row of data. What is the problem you are trying to solve?

SELECT /*+PUSH_SUBQ */
   DECODE(ROWNUM,

                      1 , id||'-'||type||'-'||suff ,
                       NULL
                      ) field_1 ,
   DECODE(ROWNUM,
                      2 , id||'-'||type||'-'||suff ,
                       NULL
                      ) field_2 ,
   DECODE(ROWNUM,
                      3 , id||'-'||type||'-'||suff ,
                       NULL
                      ) field_3

        id , type , suff
    From

        pop
    Group By

       id , type , suff
    )

Hope it helps,
Craig

John A. Pearson wrote in message <383C36B8.1DCC7E9_at_dee.wellesley.edu>...
>Hi,
>
> Is there a way to create a flattened table in SQL using
>such data as this:
>
>id type suff
>
>12 3 0
>12 3 0
>12 4 1
>12 4 1
>12 4 1
>12 4 1
>12 5 2
>12 5 2
>12 5 2
>
>select id||'-'||type||'-'||suff,
>?
>from pop
>?
>
>output would be:
>
>field1 field2 field3
>12-3-0 12-4-1 12-5-2
>
>thanks in advance for any ideas
>
>j
>
Received on Tue Nov 30 1999 - 08:52:32 CET