Join table to convert few rows into column

Question

Hi All,

I have two tables as shown in picture attached,
I want to join Table A with Table B to get all the record of Table A and the record from table B will become the columns.

With attached picture hope you will clearly understand the requirement and help me.

In TableB i have few more records with more categoryid and categoryname.

Thanks in advance.
Regards

Answer 1

Hi @Abhishek Jaiswal

In TableB i have few more records with more categoryid and categoryname.

It seems that you need a Dynamic Pivot query.
Check this:

DECLARE @sql_str VARCHAR(MAX)  
DECLARE @spread_elements VARCHAR(MAX)  
  
SELECT @spread_elements = ISNULL(@spread_elements + ',','') + QUOTENAME(Categoryname)   
FROM TableA A JOIN TableB B ON A.SrNo=B.SrN  
GROUP BY Categoryname  
--PRINT @spread_elements  
SET @sql_str ='   
;WITH CTE AS  
(  
 SELECT A.*,B.Categoryid,B.Categoryname  
 FROM TableA A JOIN TableB B ON A.SrNo=B.SrN  
)  
SELECT *   
FROM CTE   
PIVOT(MAX(Categoryid) FOR Categoryname IN('+ @spread_elements +'))P'  
  
--PRINT (@sql_str)  
EXEC (@sql_str)  

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Create table TableA  
(  
SrNo int,  
Id int,  
Name varchar(20),  
Age int,  
City varchar(20),  
Country varchar(20)  
);  
  
Insert into TableA  
Values  
(1001,11,'Abc',34,'Test1','Test'  
);  
  
Create table TableB  
(  
SrN int,  
Categoryid int,  
Categoryname varchar(20)  
);  
  
Insert into TableB  
Values  
(1001,1,'car'),  
(1001,2,'bike'),  
(1001,3,'cycle'),  
(1002,4,'plane')  
;  
  
 Select a.*,b.car,b.bike,b.cycle   from    [dbo].[tablea] a JOIN (  
 Select SrN,  
 max(Case when [categoryname]='car' then [categoryid] else null end ) [car]  
 ,max(Case when [categoryname]='bike' then [categoryid] else null end ) [bike]  
 ,max(Case when [categoryname]='cycle' then [categoryid] else null end ) [cycle]  
      
 from   [dbo].[tableb]  
 group by SrN) b on b.SrN=a.SrNo ;  
drop TABLE [dbo].TableB   
  
drop TABLE [dbo].TableA