SQL Pivot table splitting rows on custom column names. I need 1 row per OrderId

Question

I have this PIVOT table below. I have to dynamically create column headings based on the itemid value (1 to 50) in each order. then horizontally list them. in this example, it works, but it splits the rows. what can I do to change that and group them?

I feel I missing one thing here and can't figure it out. If I comment out the ProductDesc pieces, it behaves correctly with just item1.item_id and item2.item_id which displays the SKU horizontally. Now I need item1.item_name and item2.item_name to display next to the item_id's horizontally. In this example, I achieve that, but lines are split.

any help would be greatly appreciated

Results currently that need to group up into 1 row

CREATE TABLE #table1 (orderid nvarchar(256), itemid varchar(10),productid nvarchar(256), ProductDesc varchar(max))
insert into #table1
values
('001323232','1','ABC30013', 'LOVESEAT 3PC'),
('001323232','2','DFE30013', 'SOFA DINING SET')
--select * from #table1
Select*
 
 
From
(
 Select 
 'HG-KKSHGSRMN1XHG'as measurement_id ,
 'purchase' as event_name ,
 'https://www.test.com/' as [event_param.page_hostname], 
 '1665100800' as timestamp_micros,
 'offline' as [event_param.campaign_name],
 'In-Store Sales' as [event_param.campaign_source] ,
 'item'+cast(Itemid as varchar) + '.item_id' as ItemId_ProductId ,
 OrderId, 
 ProductId as ProductId,
 -- ItemId as ItemId
 ProductDesc as ProductDesc,
 -- concat('item',row_number() over (partition by Orderid order by productid)) as ItemId_ProductId,
 -- 'item'+cast(Itemid as varchar) + '.item_name' as ItemId_ProductDesc
 'item'+cast(Itemid as varchar) + '.item_name' as ItemId_ProductDesc
 -- ROW_NUMBER() OVER(ORDER BY cast(itemid as int) ASC) AS ItemId_ProductDesc
 
 FROM #table1 
 
) src
PIVOT
(
 MAX(ProductId)
 For ItemId_ProductId in ([item1.item_id],
 [item2.item_id]
 )
) mypivot
PIVOT
(
 MAX(ProductDesc)
 For ItemId_ProductDesc in ([item1.item_name],
 [item2.item_name]
 )
) mypivot2

Answer 1

You will need to add a MAX and group by in order to make this work. I answered a similar question here:How to pivot on multiple columns in SQL Server?

But here is an example for you using your data. You will need to just add additional item_id_003, etc. for as many as you want. However, it's up to you if you want to use PIVOT or just a lot of LEFT OUTER JOIN's to get the same result, the performance will be about the same.

SELECT orderid 
 , MAX(item_id_001)AS item_id_001
 , MAX(item_name_001) AS item_name_001
 , MAX(item_id_002) AS item_id_002
 , MAX(item_name_002) AS item_name_002
FROM (
SELECT T.orderid 
 , T.productid 
 , T.productdesc
 , NC_ItemID = 'item_id_' + RIGHT('000' + CONVERT(VARCHAR(10), T.itemid), 3)
 , NC_ItemName = 'item_name_'+ RIGHT('000' + CONVERT(VARCHAR(10), T.itemid), 3)
FROM #table1 AS T
 ) AS S
 PIVOT (MAX(productid) FOR NC_ItemID IN (item_id_001, item_id_002)) AS pvtitemid
 PIVOT (MAX(ProductDesc) FOR NC_ItemName IN (item_name_001, item_name_002)) AS pvtprodd 
GROUP BY orderid