Merge Multiple SQL rows into single row with multiple columns

Shabbir Daruwala 176 Reputation points
2021-09-02T15:08:35.537+00:00

Hi All,

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

128688-single.png

Below is multiple rows

128689-multiple.png

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
Transact-SQL
Transact-SQL
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
    2021-09-03T03:22:48.837+00:00

    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.

    1 person found this answer helpful.
    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Shabbir Daruwala 176 Reputation points
    2021-09-03T14:33:22.66+00:00

    Thank you very much

    0 comments No comments