expand table to fit certain format

Database
Enthusiast

expand table to fit certain format

Hello,

I have a table like this

1.PNG

It records the price of items among 7 days in a week if the item has selling record on that day. Thus, the item price on days that they don't get sold does not show up in this table, which are the records I want to add on. The output table I need is:

2.PNG

as you can see, the rows highlighted in yellow are the rows I added, to make each item has complete record in 7 days a week. and the price on the date I added is 0.

 

My idea is to create a table with 21 rows and 3 columns with predefined value for column 'keyword' and 'day', and null on price, and then join it with the original table.  But my data is of large size, do you have any simpler method to suggest? Thanks a lot!

4 REPLIES
Junior Contributor

Re: expand table to fit certain format

Is this just some example or your actual task?

What's the actual datatype of the DAY column, BYTEINT or a DATE?

How many rows exist?

Enthusiast

Re: expand table to fit certain format

Hi Dieter,

 

It's my actual task, the actual datatype of the DAY column is BYTEINT. 

The number of unique 'keyword' is about 10K, i.e there would be 10K * 7 rows in the expanded data set.

 

 

Thanks!

Junior Contributor

Re: expand table to fit certain format

Your approach sounds ok, just do everthing in a single Select, create the matrix using a Cross Join and then Left Join to your existing data

 

You need a helper table with the numbers 1 to 7 in it.

SELECT k.keyword, nbr.i,
    Coalesce(t.price, 0)
FROM 
   -- of course you might have another table where you might get the keywords from
 ( SELECT DISTINCT keyword FROM tab ) AS k
-- the numbers table CROSS JOIN numbers AS nbr
LEFT JOIN tab AS t ON t.keyword = dt.keyword AND t.day = nbr.i
Enthusiast

Re: expand table to fit certain format

Thanks Dieter!