Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2014
    Posts
    35

    Unanswered: Challenge with Pivoting data

    Hi Everyone,

    I have an interesting challenge which involves extracting data from two related databases, and pivoting part of the data from the second.

    Where I work we use SAP Business One (ERP) in concert with Accellos (WMS). Within our Warehouses we store items in many bin locations. Bin locations; items in those locations, along with quantities, etc are stored in the Accellos database. Master data related to the items themselves, such as the item cost, preferred supplier, etc is stored in SAP Business One.

    Whilst I have been able to create reports which successfully bridge both SAP & Accellos, such as that shown below, I have not been able to present the data output in an ideal format.

    Click image for larger version. 

Name:	2rqgk9i.jpg 
Views:	1 
Size:	25.6 KB 
ID:	15931

    As can be seen above given a single item code (e.g.: DR1124) there are many bin labels (and corresponding quantities) returned.

    I would like to show the bin labels 'horizontally' in the fashion illustrated below -

    Click image for larger version. 

Name:	nva1zm.jpg 
Views:	1 
Size:	25.2 KB 
ID:	15932

    I believe that using a Pivot is pivotal (excuse the pun!) to success in my endeavour, and due to this I have studied up on Pivots, both the Static type (which I am now comfortable with) and the Dynamic type (which I am still getting 'my head around').

    However there are a couple of challenges related to my specific pivot.

    • The maximum number of Bins (and correspondingly Bin Labels) per Item change
    • There are over 10K Bin Labels


    I have written a basic Dynamic Pivot which shows all Bin Labels horizontally, like so...

    Code:
    DECLARE @SQL nvarchar(max), @Columns nvarchar(max)
    
    SELECT @Columns = 
    COALESCE(@Columns + ', ', '') + QUOTENAME(BINLABEL)
    FROM
    (
    	SELECT DISTINCT
    	BINLABEL
    	FROM A1Warehouse..BINLOCAT
    ) AS B
    ORDER BY B.BINLABEL
    
    SET @SQL = '
    
    WITH PivotData AS
    (
    	SELECT
    	BINLABEL
    	, PRODUCT
    	, QUANTITY
    
    	FROM A1Warehouse..BINLOCAT
    )
    
    SELECT
    PRODUCT,
    '+ @Columns +'
    
    FROM PivotData
    PIVOT
    (
    	SUM(QUANTITY)
    	FOR BINLABEL
    	IN('+ @Columns +')
    ) AS PivotResult'
    
    EXEC(@SQL)
    Click image for larger version. 

Name:	8z9s.jpg 
Views:	1 
Size:	122.3 KB 
ID:	15933

    The above technique gives me over 10K columns because there are that many Bin Labels in total.

    It occurred to me that I would need to count the maximum number of Bin Labels for the Item that had the most Bin Labels, and that this number would then need to be used to set the maximum number of columns.

    Code:
    DECLARE @maxBins int
    DECLARE @loopCount int = 1
    
    SET @maxBins = (SELECT MAX([# of Bins]) AS 'Max Bins'
    	FROM
    	(
    		SELECT
    		COUNT(BINLABEL) '# of Bins'
    
    		FROM A1Warehouse..BINLOCAT
    
    		GROUP BY PRODUCT
    	) AS T0)
    
    PRINT @maxBins
    At this point in time one item occupies a total of 26 bin labels / locations. Every other item occupies less than 26 bin labels / locations, so I now know that I need to number my vertical columns as 'Bin 1', 'Bin 2', 'Bin 3', 'Bin...', 'Bin 26'.

    This is where the fun starts, I don't exactly need a Dynamic Pivot, but neither is a Static Pivot up to the task (at least not as best I can tell).

    Here is the Static Pivot query that I have written -

    Code:
    DECLARE @fromDate DATE = DATEADD(YY, -1, GETDATE())
    DECLARE @toDate DATE = GETDATE()
    DECLARE @maxBins int
    DECLARE @loopCount int = 1
    
    SET @maxBins = (SELECT MAX([# of Bins]) AS 'Max Bins'
    	FROM
    	(
    		SELECT
    		COUNT(BINLABEL) '# of Bins'
    
    		FROM A1Warehouse..BINLOCAT
    
    		GROUP BY PRODUCT
    	) AS T0)
    
    PRINT @maxBins
    
    SELECT *
    
    FROM
    (
    	SELECT
    	Tx.[Item Code]
    	, Tx.Description
    	, SUM(Tx.[Sales (last 12 Months)]) AS 'Sales (last 12 Months)'
    	, ISNULL(Tx.[Supplier Code], '') AS 'Supplier Code'
    	, ISNULL(Tx.[Supplier Name], '') AS 'Supplier Name'
    	, Tx.OnOrder
    	, Tx.IsCommited
    	, Tx.OnHand
    	, ISNULL(Tx.BINLABEL, '') AS 'Binlabel'
    	, ISNULL(CAST(Tx.QUANTITY AS nvarchar), '') AS 'Quantity'
    
    	FROM
    	(
    		SELECT
    		T0.ItemCode AS 'Item Code'
    		, T0.Dscription AS 'Description'
    		, SUM(T0.Quantity) AS 'Sales (last 12 Months)'
    		, T3.CardCode AS 'Supplier Code'
    		, T3.CardName AS 'Supplier Name'
    		, T2.OnOrder
    		, T2.IsCommited
    		, T2.OnHand
    		, T4.BINLABEL
    		, T4.QUANTITY
    
    		FROM INV1 T0
    		INNER JOIN OINV T1 ON T1.DocEntry = T0.DocEntry AND T1.CANCELED = 'N'
    		INNER JOIN OITM T2 ON T2.ItemCode = T0.ItemCode
    		LEFT JOIN OCRD T3 ON T3.CardCode = T2.CardCode
    		LEFT JOIN A1Warehouse..BINLOCAT T4 ON T4.PRODUCT = T0.ItemCode collate SQL_Latin1_General_CP850_CI_AS
    
    		WHERE T1.DocDate >= @fromDate AND T1.DocDate <= @toDate
    
    		GROUP BY T0.ItemCode, T0.Dscription, T3.CardCode, T3.CardName, T2.OnOrder, T2.IsCommited, T2.OnHand, T4.BINLABEL, T4.QUANTITY
    
    		UNION ALL
    
    		SELECT
    		T0.ItemCode AS 'Item Code'
    		, T0.Dscription AS 'Description'
    		, -SUM(T0.Quantity) AS 'Sales (last 12 Months)'
    		, T3.CardCode AS 'Supplier Code'
    		, T3.CardName AS 'Supplier Name'
    		, T2.OnOrder
    		, T2.IsCommited
    		, T2.OnHand
    		, T4.BINLABEL
    		, T4.QUANTITY
    
    		FROM RIN1 T0
    		INNER JOIN ORIN T1 ON T1.DocEntry = T0.DocEntry
    		INNER JOIN OITM T2 ON T2.ItemCode = T0.ItemCode
    		LEFT JOIN OCRD T3 ON T3.CardCode = T2.CardCode
    		LEFT JOIN A1Warehouse..BINLOCAT T4 ON T4.PRODUCT = T0.ItemCode collate SQL_Latin1_General_CP850_CI_AS
    
    		WHERE T1.DocDate >= @fromDate AND T1.DocDate <= @toDate
    
    		GROUP BY T0.ItemCode, T0.Dscription, T3.CardCode, T3.CardName, T2.OnOrder, T2.IsCommited, T2.OnHand, T4.BINLABEL, T4.QUANTITY
    	)Tx
    	GROUP BY Tx.[Item Code], Tx.Description, Tx.[Supplier Code], Tx.[Supplier Code], Tx.[Supplier Name], Tx.OnOrder, Tx.IsCommited, Tx.OnHand, Tx.BINLABEL, Tx.QUANTITY
    )Ty
    PIVOT
    (
    	MAX(Ty.Quantity)
    	FOR Ty.Binlabel IN ([0], [1], [2])
    )Tz
    Here is a screen shot of the results that I see -

    Click image for larger version. 

Name:	2vb4jzo.jpg 
Views:	1 
Size:	110.2 KB 
ID:	15934

    I understand why there are NULLs in my 0, 1, and 2 columns...there simply aren't Bin Labels called 0, 1 or 2!

    My challenge is that I do not know how to proceed from here. Firstly how do I call each of the pivoted columns 'Bin 1', 'Bin 2', 'Bin...', 'Bin 26' when the actual Bin Labels are over 10 thousand different possible character sets, e.g.: #0005540, K1C0102, etc, etc, etc...

    I have considered the possibility that a WHILE loop may be able to serve in populating the column names...

    Code:
    DECLARE @maxBins int
    DECLARE @loopCount int = 1
    
    SET @maxBins = (SELECT MAX([# of Bins]) AS 'Max Bins'
    	FROM
    	(
    		SELECT
    		COUNT(BINLABEL) '# of Bins'
    
    		FROM A1Warehouse..BINLOCAT
    
    		GROUP BY PRODUCT
    	) AS T0)
    
    PRINT @maxBins
    
    WHILE @loopCount <= @maxBins
    
    BEGIN
    	PRINT @loopCount
    	SET @loopCount = @loopCount +1
    END
    ...of course the query above has no practical application at this stage, but I thought that it may be useful from a 'logic' point of view.

    I have tried to insert a WHILE clause into various locations within the Static Pivot query that I wrote, however in each instance there were errors produced by SSMS.

    If anybody can suggest a way to solve my data pivoting challenge it will be much appreciated.

    Kind Regards,

    David
    Last edited by Darts75; 10-27-14 at 22:48.

  2. #2
    Join Date
    Jan 2003
    Location
    Minneapolis
    Posts
    58
    Over the course of your post, I am not entirely sure I understand for what you are looking. It almost looks like you are trying to pivot each product with their respective bin locations, but you do not want to replicate all of the bin locations since they are innumerable, but rather have the columns iterate (Bin1, Bin2, Bin3, etc.) over your results, correct?

    You can manage this through your dynamic SQL above by defining your columns through the ROW_NUMBER function.

    Code:
    DECLARE @SQL nvarchar(max), @Columns nvarchar(max)
    
    SELECT @Columns = 
    COALESCE(@Columns + ', ', '') + QUOTENAME(BINCOL)
    FROM
    (
    	SELECT DISTINCT
    	'Bin' + CONVERT(varchar,ROW_NUMBER() OVER (PARTITION BY Product ORDER BY Product)) AS BINCOL
    	FROM A1Warehouse..BINLOCAT
    ) AS B
    ORDER BY B.BINCOL
    
    SET @SQL = '
    
    WITH PivotData AS
    (
    	SELECT
    	''Bin'' + CONVERT(varchar,ROW_NUMBER() OVER (PARTITION BY Product ORDER BY Product)) AS BINCOL
    	, PRODUCT
    	, QUANTITY
    
    	FROM A1Warehouse..BINLOCAT
    )
    
    SELECT
    PRODUCT,
    '+ @Columns +'
    
    FROM PivotData
    PIVOT
    (
    	MIN(BINLABEL)
    	FOR BINCOL
    	IN('+ @Columns +')
    ) AS PivotResult'
    
    EXEC(@SQL)
    Note, this will only give you your bin location in the pivot. Quantity would not be part of your output unless you decide to concatenate the two into one delimited field in your pivot output.

  3. #3
    Join Date
    Mar 2014
    Posts
    35
    Hello Brian,

    Firstly, thank you very much for sharing your knowledge on this topic. Your code comes very close to achieving the outcome that I seek.

    I am hoping to refine the output a little more, as can be seen below the column numbering is 'logical to a computer' but not very 'human readable'...

    Click image for larger version. 

Name:	548yfk.jpg 
Views:	4 
Size:	101.6 KB 
ID:	15943

    Can you think of any way that I can rearrange the columns in the form of 'Bin1', 'Bin2', 'Bin...', 'Bin26'?

    Kind Regards,

    Davo

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Pad to two (or three) characters i.e. Bin01, Bin02, Bin03... Bin10...
    Code:
    ...
    'Bin' + Right('00' + Convert(varchar, Row_Number() OVER (PARTITION BY Product ORDER BY Product)), 2) As BINCOL
    ...
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •