-1

Imagining I have the following table:

Food served per month

We can see that Tuna was not served in Month 1. I would like to make it appear also in Month 1.

Food server per month goal

How can I achieve this?

Thank you !

wr_lcb_ck
  • 85
  • 2
  • 9
  • 1
    What have you tried so far to achieve this? Please post your attempts first. I *assume* you have a table of food types, and a table of months? – Thom A Nov 06 '18 at 15:17

3 Answers3

2

You can try to use two subqueries with DISTINCT and CROSS JOIN 

SELECT *
FROM (SELECT DISTINCT Food FROM T) t1
CROSS JOIN (SELECT DISTINCT [Month] FROM T) t2

sqlfiddle

D-Shih
  • 44,943
  • 6
  • 31
  • 51
  • Nice, similar approach – S3S Nov 06 '18 at 15:48
  • Thanks for the help. I gave the 1st reply the correct answer – wr_lcb_ck Nov 06 '18 at 16:05
  • 1
    I consider this the better answer. First, it directly gets all combinations of food and month quite clearly. Second, by limiting the distinct to the subqueries -- rather than on the entire results -- it should be more efficient. – Gordon Linoff Nov 06 '18 at 16:28
1

You can use cross apply to give each food every month. That seems to be what you want.

declare @table table (Food varchar(16), [Month] int)
insert into @table
values
('Pizza',1),
('Burgers',1),
('Salad',1),
('Tuna',2),
('Pizza',2),
('Burgers',2),
('Salad',2)

select distinct
    f.Food
    ,m.Month
from @table f
cross apply (select distinct [Month] from @table) m
order by m.Month

To find out what months Tuna was not served in...

select distinct [MONTH]
from @table
where [Month] not in (select [Month] from @table where Food = 'Tuna')
S3S
  • 24,809
  • 5
  • 26
  • 45
  • 1
    Thanks this does the job, will accept it. Also the others but you were first. One question, what query could I use if I wanted to check for example which months tuna was not served? Could you add to your answer please ? – wr_lcb_ck Nov 06 '18 at 15:53
  • done @LuisDoCantoBrum but usually new questions should be asked in a new thread so they gain visibility. Since this one was simple I added it – S3S Nov 06 '18 at 16:03
  • 1
    Thanks a lot for the help – wr_lcb_ck Nov 06 '18 at 16:05
0

If I understand the issue is that you have a table without the record of the month for some type of food. For me the best way its to create a table "month_of_year" that will contains only the number from 1 to 12.

then:

select distinct Food into #tmp from your_table

select a.Food, month_of_year.month from #tmp a cross join month_of_year

drop table #tmp

then if you want for example see the total of food for the year, go in left from the last table that I have written with the table of the sum, and you will have all the food/month with the total and in case a null on the total where the food was not be served

If you need more information ask =)

Dona
  • 78
  • 8