CODEWITHSUNDEEP
sqlsql-server
ocean800
ocean800

Reputation: 3727

Appending rows for every unique value in column?

[edit] SqlFiddle

So I have a table, something like this:

Food   | Meal      | Amount
Banana | Breakfast | 10 
Banana | Lunch     | 14
Apple  | Breakfast | 40
Apple  | Lunch     | 12

and I want to have a view that returns a table to concatenates some rows (in this case the two rows with Carrot and Spinach) per every unique Food/Meal combination. I.e something like:

Food      | Meal       | Amount 
Banana    | Breakfast  | 10
Carrot    | Breakfast  |  5
Spinach   | Breakfast  |  7
Banana    | Lunch      | 14
Carrot    | Lunch      |  5
Spinach   | Lunch      |  7
Apple     | Breakfast  | 40
Carrot    | Breakfast  |  5
Spinach   | Breakfast  |  7
Apple     | Lunch      | 12
Carrot    |   Lunch    | 5
Spinach   | Lunch      | 7

etc etc. I have seen examples of how to get this into a table like this, but a) that's to insert into a table, and I want to create this in a view, b) I want to "append" multiple rows, not just one.

What would be a good way to go about this?

Upvotes: 1

Views: 72

Answers (1)

Kazi Mohammad Ali Nur Romel
Kazi Mohammad Ali Nur Romel

Reputation: 15893

One approach can be using union all with cte

 create table table1(Food   varchar(50), Meal      varchar(50), Amount int);
 insert into table1 values('Banana' , 'Breakfast' , 10);
 insert into table1 values('Banana' , 'Lunch'     , 14);
 insert into table1 values('Apple'  , 'Breakfast' , 40);
 insert into table1 values('Apple'  , 'Lunch'     , 12);

Query:

 with cte as
 (
   select 'Carrot' food, 5 amount
   union all
   select 'Spinach' food,7 amount
 )
 select * from table1 
 union all
 select food,meal,amount from cte cross join (select  meal from table1)t

Output:

Food Meal Amount
Banana Breakfast 10
Banana Lunch 14
Apple Breakfast 40
Apple Lunch 12
Carrot Breakfast 5
Spinach Breakfast 7
Carrot Lunch 5
Spinach Lunch 7
Carrot Breakfast 5
Spinach Breakfast 7
Carrot Lunch 5
Spinach Lunch 7

db<fiddle here

To have the output in your desired order:

Query: with cte as ( select 'Carrot' food, 5 amount union all select 'Spinach' food,7 amount ) ,cte2 as ( select food,meal,amount,row_number()over(order by (select null))rn from table1 ) ,cte3 as ( select food,meal,amount,rn from cte2 union all select cte.food,cte2.meal,cte.amount,rn from cte2 cross join cte ) select food,meal,amount from cte3 order by rn,food

Output:

food meal amount
Banana Breakfast 10
Carrot Breakfast 5
Spinach Breakfast 7
Banana Lunch 14
Carrot Lunch 5
Spinach Lunch 7
Apple Breakfast 40
Carrot Breakfast 5
Spinach Breakfast 7
Apple Lunch 12
Carrot Lunch 5
Spinach Lunch 7

db<fiddle here

Upvotes: 1

Related Questions