Merge Multiple SQL rows into single row with multiple columns

Question

Hi All,

I want to convert multiple rows into single with below expected output

Below is multiple rows

Answer 1

Hi @Shabbir Daruwala ,

Welcome to the microsoft TSQL Q&A forum!

As Erland said, for such problems, we recommend that you provide related CREATE and INSERT statements so that we can directly copy the code to test.

Please refer to:

CREATE TABLE #Detailnew(id INT,ASMapid INT, Detaillabelid INT,Detailvalueid INT,  
Detailvaluetext VARCHAR(25))  
INSERT INTO #Detailnew VALUES  
(1,18835,1,1,null),  
(2,18835,1,2,null),  
(3,18835,2,null,'28'),  
(4,18835,3,null,'28 regular in '),  
(5,18835,4,null,'28 in'),  
(6,18835,5,6,null),  
(7,18835,6,7,null),  
(8,18835,6,8,null)  
  
CREATE TABLE #Mapset(id INT,Settypeid INT, [Description] VARCHAR(25),Isfreetext INT)  
INSERT INTO #Mapset VALUES  
(1,1,'Body fit',0),  
(2,2,'Size',1),  
(3,2,'Length',1),  
(4,3,'Trouser Size',1),  
(5,3,'Length',0),  
(6,3,'Big & Tall',0)  
  
CREATE TABLE #Mapsetvalue(id INT,ASMapSetid INT, [Value] VARCHAR(25))  
INSERT INTO #Mapsetvalue VALUES  
(1,1,'Cure'),  
(2,1,'Tall'),  
(3,2,'28'),  
(4,3,'28 regular in '),  
(5,4,'28 in'),  
(6,5,'Regular'),  
(7,6,'Big'),  
(8,6,'Tall')  
  
;WITH cte  
as(SELECT ROW_NUMBER() OVER(ORDER BY ASMapid) id,ASMapid,[Description],[Value]   
FROM #Detailnew d  
JOIN #Mapset s ON d.id=s.id  
JOIN #Mapsetvalue v ON s.id=v.ASMapSetid)  
,cte2 as(SELECT * FROM (SELECT id,ASMapid,[Description] FROM cte) c  
PIVOT (MAX([Description]) FOR id IN ([1],[2],[3],[4],  
[5],[6],[7],[8])) p)  
,cte3 as(SELECT * FROM (SELECT id,ASMapid,[Value] FROM cte) c  
PIVOT (MAX([Value]) FOR id IN ([1],[2],[3],[4],  
[5],[6],[7],[8])) p)  
  
SELECT c2.ASMapid,c2.[1] as FitSizeLabel1,c3.[1] as FitSizeValue1,  
c2.[2] as FitSizeLabel2,c3.[2] as FitSizeValue2,  
c2.[3] as SplitSizeLabel1,c3.[3] as SplitSizeValue1,  
c2.[4] as SplitSizeLabel2,c3.[4] as SplitSizeValue2,  
c2.[5] as SearchLabel1,c3.[5] as SearchValue1,  
c2.[6] as SearchLabel2,c3.[6] as SearchValue2,  
c2.[7] as SearchLabel3,c3.[7] as SearchValue3,  
c2.[8] as SearchLabel4,c3.[8] as SearchValue4  
FROM cte2 c2  
JOIN cte3 c3  
ON c2.ASMapid=c3.ASMapid  

If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
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

Thank you very much