Assistance with Pivot in sql

Below is my current qry
..

I need to see the records in the Size column broken out into their own columns…with a summary of FinalQty by Root and PONum… How is this possible

SELECT     CreationDate, InHand, PONum, ShipDate, VendorName, SKU, Root, Size, Description, FinalQty, Tracking, Notes

FROM         (SELECT     BV_Item.CreationDate, BV_Item.InHand, BV_ItemDetail.PONum, BV_ItemDetail.ShipDate, BV_ItemDetail.VendorName, BV_ItemDetailCombination.SKU,
                                              LEFT(BV_ItemDetailCombination.SKU, 9) AS Root, SUBSTRING(BV_ItemDetailCombination.SKU, 15, 4) AS Size, BV_ItemDetailCombination.Description,
                                              BV_ItemDetailCombination.FinalQty, BV_PO_Status.Tracking, BV_PO_Status.Notes
                       FROM          BV_Item INNER JOIN
                                              BV_ItemDetail ON BV_Item.ItemID = BV_ItemDetail.ItemID INNER JOIN
                                              BV_ItemDetailCombination ON BV_ItemDetail.ItemDetailID = BV_ItemDetailCombination.ItemDetailID LEFT OUTER JOIN
                                              BV_PO_Status ON BV_ItemDetail.PONum = BV_PO_Status.PurchaseOrder
                       WHERE      (BV_ItemDetail.Status < 40) AND (BV_Item.PGMID <> 'CUST') AND (BV_ItemDetail.ShipCompany LIKE 'BrandVia%') AND (BV_Item.Status < 50) AND
                                              (BV_ItemDetail.PONum NOT LIKE 'TBD%') AND (BV_ItemDetailCombination.LineType = 'Stocked'))U
Michael KatzAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

arnoldCommented:
Can you post the sample data and what it is you want to see?


https://technet.microsoft.com/en-us/library/ms177410%28v=sql.105%29.aspx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DcpKingCommented:
" the records in the Size column broken out into their own columns"

What do you mean by "their" own columns. Size is usually a smoothly varying attribute unless you have it quantised somehow - 0-10, 11-20, 20-30, etc. If you do you have given no indication: to use the T SQL PIVOT command you have to be able to define the column names at runtime - which often means creating dynamic SQL because you don't know the (in this case required size quanta ?) until then.

Therefore, as arnold says, you need to provide at least an example of some data and what it would transform into.
0
Michael KatzAuthor Commented:
Sorry I was not specific… the sizes I need are  XS,S,M,L,XL,2XL,3XL,4XL from the 'SIZE' Field.. The below attachment is a screen shot of the data
Sample.jpg
0
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

arnoldCommented:
So you want the data as


Desc/size columns of sizes size .... size
Each row will contain the description and the count of size available in inventory.

You might have too many columns for referencing.

You can only use three total, size is one that will create the columns. Root is the base of the sku presumably equivalent to the description ponum presumably is the parameter of

It will take some digesting given the heading in the sample are altered in the query you posted.

I gather you have raw data though it seems you are trying to get a report that mixes two sets of circumstances.
One deals with listing the inventory you have in a pivot table, in the other option you seem to want the pivot breakdown of po,sku(general),sizes and quantity that is being purchased.
0
Michael KatzAuthor Commented:
You have the concept correct....hopefully I can have a summary by po, and sku...
0
arnoldCommented:
The query you posted is that what the image represents?
You can not use sku, root is the portion of the sku.

I am not sure what you mean by summary.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
For the example you posted as screenshot, can you post the desired output?

This query don't gives what you want?
SELECT CreationDate, InHand, PONum, ShipDate, VendorName, SKU, Root, Size, Description, SUM(FinalQty), Tracking, Notes
FROM (SELECT BV_Item.CreationDate, BV_Item.InHand, BV_ItemDetail.PONum, BV_ItemDetail.ShipDate, BV_ItemDetail.VendorName, BV_ItemDetailCombination.SKU, 
			LEFT(BV_ItemDetailCombination.SKU, 9) AS Root, SUBSTRING(BV_ItemDetailCombination.SKU, 15, 4) AS Size, BV_ItemDetailCombination.Description, 
            BV_ItemDetailCombination.FinalQty, BV_PO_Status.Tracking, BV_PO_Status.Notes
		FROM BV_Item 
			INNER JOIN BV_ItemDetail ON BV_Item.ItemID = BV_ItemDetail.ItemID 
			INNER JOIN BV_ItemDetailCombination ON BV_ItemDetail.ItemDetailID = BV_ItemDetailCombination.ItemDetailID 
			LEFT OUTER JOIN BV_PO_Status ON BV_ItemDetail.PONum = BV_PO_Status.PurchaseOrder
		WHERE (BV_ItemDetail.Status < 40)
			 AND (BV_Item.PGMID <> 'CUST')
			 AND (BV_ItemDetail.ShipCompany LIKE 'BrandVia%')
			 AND (BV_Item.Status < 50)
			 AND (BV_ItemDetail.PONum NOT LIKE 'TBD%')
			 AND (BV_ItemDetailCombination.LineType = 'Stocked'))U
GROUP BY CreationDate, InHand, PONum, ShipDate, VendorName, SKU, Root, Size, Description, Tracking, Notes

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.