By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table [Table-A](Productid int,Brandname varchar(10));
insert into [Table-A] values(1 ,'C Brand');
insert into [Table-A] values(2 ,'K Brand');
insert into [Table-A] values(3 ,'A Brand');
create table [Table-B] (Productid int, Rate int , Slab int)
insert into [Table-B] values(1 ,10 ,1);
insert into [Table-B] values(2 ,20 ,1);
insert into [Table-B] values(3 ,30 ,1);
insert into [Table-B] values(1 ,12 ,2);
insert into [Table-B] values(2 ,22 ,2);
insert into [Table-B] values(3 ,32,2);
9 rows affected
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT ',' + QUOTENAME(Brandname) from [Table-A] order by productid FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'),1,1,'')
set @query = 'SELECT Slab,' + @cols + '
from
(
select a.BrandName, b.Rate,b.Slab from [Table-A] a inner join [Table-B] b on a.productid=b.productid
) x
pivot
(
max(Rate)
for Brandname in (' + @cols + ')
) p '
execute(@query)
| Slab | C Brand | K Brand | A Brand |
|---|---|---|---|
| 1 | 10 | 20 | 30 |
| 2 | 12 | 22 | 32 |
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select distinct @cols=STRING_AGG(quotename(brandname),',') from [Table-A]
set @query = 'SELECT Slab,' + @cols + '
from
(
select a.BrandName, b.Rate,b.Slab from [Table-A] a inner join [Table-B] b on a.productid=b.productid
) x
pivot
(
max(Rate)
for Brandname in (' + @cols + ')
) p '
execute(@query)
| Slab | C Brand | K Brand | A Brand |
|---|---|---|---|
| 1 | 10 | 20 | 30 |
| 2 | 12 | 22 | 32 |