1 Reply Latest reply: Jul 11, 2014 10:20 AM by BluShadow RSS

    Rows -> Columns




      I have 2 tables, example:

      - Location:


      OID     Location_name


      oid1          Loc1

      oid2          Loc2

      oid3          Loc3


      - Ticketcheck:


      .. user_id     Location_oid    ..    

           1                    loid1

           1                    loid2

           2                    loid3

           3                    loid1

           3                    loid2


      I need results:


      user_id      Loc1       Loc2     Loc3

      1                 1              1         0

      2                 0              0         1

      3                 1              1         0


      Where numbers are count for that location for that user.

      I can do it with decode function:


      select user_id,

                sum(decode,loid1,1) "Loc1",

                sum(decode,loid2,1) "Loc2",

                sum(decode,loid3,1) "Loc2"

      from Ticketcheck

      group by userid


      but problem is that I could have 10,20,30 or more locations and soneone could add more, so everytime somebody add one location I need to change my query. And ideas? Thanks for help.


      PS. Be gentle, its my first post here. And sorry for my bad english.

        • 1. Re: Rows -> Columns

          So you're looking for a dynamic pivot.

          Please see the forum FAQ: Re: 4. How do I convert rows to columns?


          My answer would be, why would you want a query that produces an undefined number of columns in the results? What code are you going to write that can handle an unknown number of columns?

          Typically this type of requirement is something done for reporting purposes, so the best place to do it is in a reporting tool, which itself will usually query the data and then pivot it dynamically to provide the best presentation for the report.  It's not something often required inside any code processing.  So, perhaps you need to reconsider what the actual requirement is?