SQLTeam.com | Weblogs | Forums

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.

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.

select Region, a.ID, Protofolio, b.category, value
  from dtable1 a
  cross join dtable2 b
  order by Region

but portfolio 3y exists twice (3y PAR and 3y EVENT) but not included in your final desired list, is that by design?

Because you have multiple rows in 'table1' with different Categories, but you want to report on all categories - a simple cross-join isn't going to work. You need to get a distinct list of Region/ID/Protofolio (sp?) first, then cross-join that to get a list of all Region/ID/Protofolio/Categories - then outer join (left join) table1 to all categories to get the values:

   With distinctRegion
     As (
 Select Distinct
        t1.Region
      , t1.ID
      , t1.Protofolio
   From table1                          t1
        )
      , allCategories
     As (        
 Select dr.Region
      , dr.ID
      , dr.Protofolio
      , t2.Category
   From distinctRegion                  dr
  Cross Join table2                     t2
        )
 Select ac.Region
      , ac.ID
      , ac.Protofolio
      , ac.Category
      , t1.[Value]
   From allCategories                   ac
   Left Join table1                     t1 On t1.Region = ac.Region
                                          And t1.ID = ac.ID
                                          And t1.Protofolio = ac.Protofolio
                                          And t1.Category = ac.Category
   Order By
         ac.Region
       , ac.ID
       , ac.Protofolio desc
       , ac.Category;

If possible - add an additional column to 'table2' - define the column as sort order and use that to sort the results as you need them (if you need the results sorted in a particular order).