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?
 
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
 
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
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.