hello,
For a couple of days i struggle with this and I have no idea how to write the code.
i have 2 tables: table1 and table2
create table table1
(Region varchar(10) null,
ID varchar(1) null,
Protofolio varchar(100) null,
Category varchar(50) null,
Value float null)
insert into table1 values
('France','A','3y','PAR','10'),
('France','A','2y','EVENT','20'),
('France','A','1y','HYPER','35'),
('France','A','3y','EVENT','60'),
('France','A','5y','Store','11'),
('Germany','B','2y','HYPER','12')
create table table2
(Category varchar(50) null)
insert into table2 values
('PAR'),
('EVENT'),
('Hyper'),
('Volume'),
('Store'),
('Blades')
in the last table_final I need to have for every Portofolio from table1 all the Categories in table2.
As u can see in the picture,the final table has for every portfolio all the categories in table 2 and where I don't have a value I just put 0.
I wrote the code bellow but it doesn't work how I want.
SELECT a.[Region]
,a.[ID]
,a.[Protofolio]
,b.[Category]
,'' as [Value]
into #table_final
FROM [dbo].[table1] as a
inner join (select distinct [Category]from [dbo].[table2]) as b
on a.[Category]=b.[Category]
union all
select * from [dbo].[table1]
i would really appreciate your help.