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

Below is multiple rows

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

Below is multiple rows

For this type of question, you do yourself a service if you post the CREATE TABLE statements for your table and provide the sample data with INSERT statements. Then we can easily copy and paste into a query window to develop a tested solution. We cannot copy and paste from screenshots.
It also helps that you provide the expected result in an easily readable format. (That is, the image you posted is a wee bit small.)
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.
18 people are following this question.
Year and Month aggregation in same Pivot table in SQL Server
SQL Server Query for Searching by word in a string with word breakers
How to show first row group by part id and compliance type based on priorities of Document type?
Query to list all the databases that have a specific user
T-sql query to find the biggest table in a database with a clustered index