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.

    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 -

    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...

    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)

    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.

    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 -

    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 -

    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...

    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

  • Hi

    I'm pretty sure you don't require while loops to do this.

    The following should do what you want.

    WITH numberBinsPerItem AS (

    SELECT ItemCode,

    ItemDescription,

    OrderQty,

    BinLabel,

    ROW_NUMBER() OVER (PARTITION BY ItemCode ORDER BY BinLabel) BinNum

    FROM A1Warehouse..BINLOCAT

    )

    -- CrossTab Pivot

    SELECT ItemCode,

    ItemDescription,

    OrderQty,

    MAX(CASE WHEN BinNum = 1 THEN BinLabel END) BinLabel1,

    MAX(CASE WHEN BinNum = 2 THEN BinLabel END) BinLabel2,

    ...

    MAX(CASE WHEN BinNum = 26 THEN BinLabel END) BinLabel26

    FROM numberBinsPerItem

    GROUP BY ItemCode, ItemDescription, OrderQty;

    The following articles by Jeff Moden are a good read

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns[/url]

    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs[/url]

  • Hi MickyT,

    Thanks for your response, and advice. I will read the tutorials that you indicated and then study the sample query that you supplied.

    Kind Regards,

    Davo

  • Hi MickyT,

    After reading through the links that you kindly provided, and investigating the query which you generously shared I came to realize that my specific challenge is that I do not actually know the names of the Bins, so unfortunately I cannot use a CASE statement at all - e.g.: CASE WHEN BIN = 1... will not work as there is never a case when the Bin equals one, instead it will only ever 'equal it's label', e.g.: B2A1204, M1L0251, etc (up to just over 10K label possibilities...).

    On another forum I have received a response that currently comes close to achieving my desired output -

    dbForums

    Please have a look and let me know what you think.

    Kind Regards,

    David

  • In the query that I provided it assigns its own number to the bins of each item in alphabetical order.

    Run the following query to see an example

    SELECT * INTO #SAMPLE

    FROM (VALUES

    (1,'SomeLabel')

    ,(1,'AnotherLabel')

    ,(2,'randomLabel')

    ,(2,'repeatedLabel')

    ,(2,'newLabel')

    ,(2,'oldLabel')

    ,(3,'repeatedLabel')

    ,(4,'oldestLabel')

    ,(4,'newestLabel')

    ) S(ItemCode,BinLabel);

    -- This Query assigns the bins a number (alphabetical order for each item)

    SELECT ItemCode,

    BinLabel,

    ROW_NUMBER() OVER (PARTITION BY ItemCode ORDER BY BinLabel) BinNum

    FROM #Sample;

    -- Then we wrap this up in a CTE and apply a crosstab

    WITH numberBinsPerItem AS (

    SELECT ItemCode,

    BinLabel,

    ROW_NUMBER() OVER (PARTITION BY ItemCode ORDER BY BinLabel) BinNum

    FROM #SAMPLE

    )

    -- CrossTab Pivot

    SELECT ItemCode,

    MAX(CASE WHEN BinNum = 1 THEN BinLabel END) BinLabel1,

    MAX(CASE WHEN BinNum = 2 THEN BinLabel END) BinLabel2,

    MAX(CASE WHEN BinNum = 3 THEN BinLabel END) BinLabel3,

    MAX(CASE WHEN BinNum = 4 THEN BinLabel END) BinLabel4,

    MAX(CASE WHEN BinNum = 5 THEN BinLabel END) BinLabel5

    FROM numberBinsPerItem

    GROUP BY ItemCode;

    DROP TABLE #sample;

    This results in

    ItemCode BinLabel BinNum

    ----------- ------------- --------------------

    1 AnotherLabel 1

    1 SomeLabel 2

    2 newLabel 1

    2 oldLabel 2

    2 randomLabel 3

    2 repeatedLabel 4

    3 repeatedLabel 1

    4 newestLabel 1

    4 oldestLabel 2

    (9 row(s) affected)

    ItemCode BinLabel1 BinLabel2 BinLabel3 BinLabel4 BinLabel5

    ----------- ------------- ------------- ------------- ------------- -------------

    1 AnotherLabel SomeLabel NULL NULL NULL

    2 newLabel oldLabel randomLabel repeatedLabel NULL

    3 repeatedLabel NULL NULL NULL NULL

    4 newestLabel oldestLabel NULL NULL NULL

    Warning: Null value is eliminated by an aggregate or other SET operation.

    (4 row(s) affected)

    Just noticed I had a syntax error in the previous code (PARITION instead of PARTITION). Will fix it up.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply