Using SQL Lateral

I think we have probably all seen a table that we wish was designed a little differently. Like lets take a Yearly/Quarterly Sales file as an example where the programmer decided instead of making each year/quarter a unique row, they made the table with a year and 4 buckets for each quarter. Or 12 columns for each month. We now need to query by year and quarter. While this is probably a trivial task it would've been easier if I could have just said I want this year and this quarter but now I have to know the column name for each bucket. Here is a way to transform the data vertically instead of horizontal.

So let's first create a simple table.

Create Table qtemp.qtrly_sales (
      year Integer
      ,qtr1 Numeric(11, 2)
      ,qtr2 Numeric(11, 2)
      ,qtr3 Numeric(11, 2)
      ,qtr4 Numeric(11, 2)
    );        

This table contains the Year and 4 columns for each quarter in the year.

Now let's populate the table with some data.

Insert Into qtemp.qtrly_sales
  Values
    (
      2020
      ,40000
      ,35000
      ,15000
      ,50000
     )
    ,(
      2021
      ,75000
      ,80000
      ,95000
      ,85000
     )
    ,(
      2022
      ,125000
      ,130000
      ,145000
      ,150000
     )
    ,(
      2023
      ,115000
      ,120000
      ,175000
      ,203000
     );        

We can now query the table to see the data

select * from qtemp.qtrly_sales        

Now if we want to see the data vertically instead of horizontally we can use Lateral in DB2 to change the data.

Select qs.year
       ,qs.qtr
       ,qs.sales
  From qtemp.qtrly_sales d
       ,Lateral (
         Values
           (
             d.year
             ,1
             ,d.qtr1
            )
           ,(
             d.year
             ,2
             ,d.qtr2
            )
           ,(
             d.year
             ,3
             ,d.qtr3
            )
           ,(
             d.year
             ,4
             ,d.qtr4
            )
       ) As qs (year, qtr, sales);        

We can assign a constant to the QTR column (1,2,3,4) and have the sales amount listed horizontally. You could also change this SQL into a view so that it is reusable. Now you could select for a specific year and quarter easily.