Looking for a dynamic pivot table for Orders that pivot out the details based on ItemId
Items from an order are needing to be pivoted so that there is only 1 row of each order #, then corresponding details on the entire order horizontally.
For instance, there could be 2 items on order #1 and 8 items on order #2. They want an item index number on each heading. Here is what an output would look like
**This is the desired result,
OrderNumber – item1.SKU – item2.SKU – item1.SKUDesc – item2.SKUDesc
123456 K123 K456 Television 50inch – Radio 50amps –
SELECT header.OrderNumber, -- this is distinct
detail.ItemId, -- need this so it has unique Id for column naming prefix within
detail.SKU,-- needs to pivot across with unique ItemId+'item_product'
detail.SKUdescription -- needs to pivot across with unique ItemId+
FROM orders header
INNER JOIN orderlines detail on o.order = detail.order
These lines are repeated coming from a normalized table. In this case, the client wants the OrderId unique on 1 line, and any attributes dynamically added in sequence left to right. The ItemId is the unique ID for each item attribute column pre-fix on the order. So OrderNumber, then item1.x, item2.y, and so on.
I was creating MULTIPLE repeatable columns to hit it again and it kind of works using PIVOT, but it has a bunch of NULL values spaced out in rows. I tried groupings but couldn’t group them up. The NULLS space out the results. If the NULLs all went away, and it grouped up, it would look correct.
See the screen shot to see the results. If I can do 1 thing to remove those NULLs , that would solve my problem item+cast(Itemid as varchar) + ‘.item_name’ as ItemId_ProductDesc,
Thoughts? Looking for the simple result set so I can expand on static columns to the left of OrderId.