question

Duman avatar image
Duman asked

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.

imageimage790×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

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

1 Answer

·
Kev Riley avatar image
Kev Riley answered
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 comment
10 |1200

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

thank you it works

0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

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