ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

Pivot from columns to rows

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Pivot from columns to rows

    Usually people want to do the opposite... but I would like to take 12 period columns and transform them into separate rows.

    So currently after my SQL statement (it sums item sales for a specific year and class) runs, I have the following columns in one resulting row:

    Year, Period, Class, SalesQty1, Sales$1, SalesQty2, Sales$2, SalesQty3, Sales$3, ... , SalesQty12, Sales$12

    I would like 12 rows instead that look like this:

    Year, Period, Class, SalesQty1, Sales$1
    Year, Period, Class, SalesQty2, Sales$2
    Year, Period, Class, SalesQty3, Sales$3
    ...
    Year, Period, Class, SalesQty12, Sales$12

    My SQL statement is currently something like:

    Code:
    select max(GCLS) as Class, max(GFY) as Year,
           sum(GQ01) as SalesQty1, sum(GA01/100) as Sales$1,
           sum(GQ02) as SalesQty2, sum(GA01/100) as Sales$2,
           sum(GQ03) as SalesQty3, sum(GA01/100) as Sales$3,
           sum(GQ04) as SalesQty4, sum(GA01/100) as Sales$4,
           sum(GQ05) as SalesQty5, sum(GA01/100) as Sales$5,
           sum(GQ06) as SalesQty6, sum(GA01/100) as Sales$6,
           sum(GQ07) as SalesQty7, sum(GA01/100) as Sales$7,
           sum(GQ08) as SalesQty8, sum(GA01/100) as Sales$8,
           sum(GQ09) as SalesQty9, sum(GA01/100) as Sales$9,
           sum(GQ10) as SalesQty10, sum(GA01/100) as Sales$10,
           sum(GQ11) as SalesQty11, sum(GA01/100) as Sales$11,
           sum(GQ12) as SalesQty12, sum(GA01/100) as Sales$12
    from myFile
    where GCLS = :myclass
    and GFY = :myYear
    How to do this transform within my SQL statement...? TIA.

  • #2
    Viking, try this.

    Code:
    Select
    GFY as Year,
    MTH As Period,
    GCLS as Class,
    Sum(QTY) As SalesQty,
    Sum(GA01/100) As Sales$
    From
    myFile
    Cross Join Lateral
    (Values (1,GQ01),
            (2,GQ02),
            (3,GQ03),
            (4,GQ04),
            (5,GQ05),
            (6,GQ06),
            (7,GQ07),
            (8,GQ08),
            (9,GQ09),
            (10,GQ10),
            (11,GQ11),
            (12,GQ12)) As SALES_BY_MTH (MTH,QTY)
    Where GCLS = :myclass
    And GFY = :myYear
    Group By
    GFY,MTH,GCLS
    Jim

    Comment


    • #3
      Jim, thanks for responding - I was hoping you would! Your solution with the Cross Join Lateral works great.

      I see that I made a copy/paste mistake in my post by not showing that the Sales$ are also in 12 different fields (GA01 - GA12, not all in GA01), just like the 12 Quantities. But following what you showed, I made it work with the following:


      Code:
      [FONT=courier new]Select
      GFY as Year,
      MTH As Period,
      GCLS as Class,
      Sum(QTY) As SalesQty,
      Sum(AMT/100) As Sales$
      From myFile
      Cross Join Lateral
      (Values (1,GQ01,GA01),
              (2,GQ02,GA02),
              (3,GQ03,GA03),
              (4,GQ04,GA04),
              (5,GQ05,GA05),
              (6,GQ06,GA06),
              (7,GQ07,GA07),
              (8,GQ08,GA08),
              (9,GQ09,GA09),
              (10,GQ10,GA10),
              (11,GQ11,GA11),
              (12,GQ12,GA12)) As SALES_BY_MTH (MTH,QTY,AMT)
      Where GCLS = :myClass
      And GFY = :myYear
      Group By GFY,MTH,GCLS
      Order By GFY, MTH, GCLS[/FONT]
      Thank you so much! I'm learning a lot from your SQL skills.

      Comment

      Working...
      X