question

ShabbirDaruwala-7855 avatar image
0 Votes"
ShabbirDaruwala-7855 asked ShabbirDaruwala-7855 answered

Merge Multiple SQL rows into single row with multiple columns

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-generalsql-server-transact-sql
single.png (5.8 KiB)
multiple.png (27.2 KiB)
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

For Reference

SettingTypeID

1 = FitSize

2 = SplitSize

3 = Search

0 Votes 0 ·

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.)

0 Votes 0 ·
EchoLiu-msft avatar image
1 Vote"
EchoLiu-msft answered EchoLiu-msft edited

Hi @ShabbirDaruwala-7855,

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.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ShabbirDaruwala-7855 avatar image
0 Votes"
ShabbirDaruwala-7855 answered

Thank you very much

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.