How to multiply rows by category

Question

question

Duman asked Mar 5, '21

How to multiply rows by category

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.

image790×643 13.4 KB

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.

t-sql

1614948245297.png (59.3 KiB)

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2021-03-05T14:24:04Z

Kev Riley answered Mar 5, '21

with 
base_RegionIDProtofolio as (
    select distinct table1.Region, table1.ID, table1.Protofolio from table1
),
AllPermutations as (
select Region, ID, Protofolio, Category
from 
    ((select distinct table1.Region, table1.ID, table1.Protofolio from table1) base_RegionIDProtofolio cross join table2) 
)

select 
    AllPermutations.Region, 
    AllPermutations.ID, 
    AllPermutations.Protofolio, 
    AllPermutations.Category, isnull(Value ,0)
from AllPermutations
left join table1 on AllPermutations.Region = table1.Region 
    and AllPermutations.ID = table1.ID 
    and AllPermutations.Protofolio = table1.Protofolio 
    and AllPermutations.Category = table1.Category

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Duman commented · Mar 05, 2021 at 06:40 PM

thank you it works

0 ·

question