Re: How do I transpose a table?

Hi,

thanks for the reply, my problem is my subscribers number are unique. I have been reading the post here about this subject and people seem to be flipping tables on the side via some kind of category field eg: department or job.

Although my subscription_id's are unique I need to be able to spread them accross the table like this

CUSTOMERID      SUBSCRIPTIONID1 SUBSCRIPTIONID2 SUBSCRIPTIONID3
11              1               2               3
22              4               5
33              6               7
44              8

This is whats doing mu head in, otherwise I would use the decode like other people are doing?

any suggesions? Do you think I needs some interim table with extra information in it about the number of subscriptions? Even if I had this I am not sure how to spread them across the table for each customer?

Thanks

Lee

Daniel Morgan <dmorgan_at_exesolutions.com> wrote in message news:<3CE2AE5E.B5F4CC5E_at_exesolutions.com>...
> Lee Farrant wrote:
>
> > I think the word for this is transpose but I am not sure.
> >
> > I have a table which is in the following format
> >
> > Customer 1 Subscriber 1
> > Customer 1 Subscriber 2
> > Customer 1 Subscriber 3
> > Customer 1 Subscriber 4
> > Customer 2 Subscriber 5
> > Customer 2 Subscriber 6
> > Customer 3 Subscriber 7
> > Customer 3 Subscriber 8
> >
> > I need to transform this table into the following format so there is
> > only one record for each customer and the subscriber fields 1-x are
> > going across the table.
> >
> > Customer 1 Subscriber 1 Subscriber 2 Subscriber 3 Subscriber 4
> > Customer 2 Subscriber 5 Subscriber 6
> > Customer 3 Subscriber 7 Subscriber 8
> >
> > Its unusual that a customer will more than 4 subscriptions but
> > basically I will not no which customer has the most subscriptions
> > until a process the table in some way, I assume I would need this
> > information to decide how many subscription columns to create on the
> > new table. am i making sense?
> >
> > Is there any code SQL out there that will already do this that I could
> > edit as I am very stuck.
> >
> > thanks
> >
> > Lee leefarrant_at_hotmail.com
>
> You can use DECODE to create columns from fields.
>
> Daniel Morgan
Received on Thu May 16 2002 - 05:53:52 CDT