0

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
New contributor
paygboy00 is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
0

1 Answer 1

0

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 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.