Merge Multiple SQL rows into single row with multiple columns

Shabbir Daruwala 176 Reputation points

Hi All,

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


Below is multiple rows


SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,070 questions
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,577 questions
{count} votes

Accepted answer
  1. EchoLiu-MSFT 14,576 Reputation points

    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  
    (4,18835,3,null,'28 regular in '),  
    (5,18835,4,null,'28 in'),  
    CREATE TABLE #Mapset(id INT,Settypeid INT, [Description] VARCHAR(25),Isfreetext INT)  
    (1,1,'Body fit',0),  
    (4,3,'Trouser Size',1),  
    (6,3,'Big & Tall',0)  
    CREATE TABLE #Mapsetvalue(id INT,ASMapSetid INT, [Value] VARCHAR(25))  
    INSERT INTO #Mapsetvalue VALUES  
    (4,3,'28 regular in '),  
    (5,4,'28 in'),  
    ;WITH cte  
    as(SELECT ROW_NUMBER() OVER(ORDER BY ASMapid) id,ASMapid,[Description],[Value]   
    FROM #Detailnew d  
    JOIN #Mapset s ON  
    JOIN #Mapsetvalue v ON  
    ,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.


    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.

    1 person found this answer helpful.
    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Shabbir Daruwala 176 Reputation points

    Thank you very much

    0 comments No comments