Pivot Query

  • Got it, thanks.  I think part of my dilemma has been those controls are so small, and when I hover over them the descriptions are covered up by Windows icons.

    Steve Anderson

  • stephen.aa wrote:

    Phil:

    Happy to comply, but I'm not sure I know how to do those things.  I will try to figure it out.

    Other sites have controls for these things, which I do not see here.

    Please see the article at the first link in my signature line below for one way to do it.  See the post at the link that Phil Provided for another and the code in the post right below Phil's post for yet another.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • stephen.aa wrote:

    Just one more thing... The example above spans 29 months.  Is there a good way to separate it out to place three columns for each month within the date range?

    Yes but can you post an example output just so we're sure?  What I'm concerned about is that it sounds like you want 29 sets of 3 columns all spread out horizontally for a total of more than 29*3 or 87 columns wide.  That CAN be done auto-magically but I want to make sure that's what you actually want.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • IF OBJECT_ID('tempdb..#PartTransactions','U') IS NOT NULL
    DROP TABLE #PartTransactions
    GO
    SELECT *
    INTO #PartTransactions
    FROM (VALUES ('2020-12-31 19:00:09',3,7000917),
    ('2020-09-29 12:05:25',1,7000917),
    ('2020-09-28 06:56:42',2,7000917),
    ('2019-12-31 23:59:30',3,7000917),
    ('2019-09-07 17:38:34',3,7000917),
    ('2019-09-07 17:38:34',3,7000917),
    ('2020-09-29 12:05:25',1,7000918),
    ('2020-09-28 06:56:42',2,7000918),
    ('2019-12-31 23:59:30',3,7000918),
    ('2019-09-07 17:38:34',3,7000918),
    ('2019-09-07 17:38:34',3,7000918),
    ('2019-09-07 17:16:44',3,7000917))T(imtTransactionDate, imtTransactionType, [imtPartID])
    GO
    SELECT pt.[imtPartID],
    SUM(CASE WHEN pt.imtTransactionType = 1 THEN 1 ELSE 0 END) AS Receipt,
    SUM(CASE WHEN pt.imtTransactionType = 2 THEN 1 ELSE 0 END) AS Issue,
    SUM(CASE WHEN pt.imtTransactionType = 3 THEN 1 ELSE 0 END) AS Adjustment
    FROM #PartTransactions pt
    WHERE pt.imtTransactionDate BETWEEN '2019-01-01' AND '2021-01-01'
    GROUP BY pt.[imtPartID];
  • stephen.aa wrote:

    Just one more thing... The example above spans 29 months.  Is there a good way to separate it out to place three columns for each month within the date range?

    Include the month in the GROUP BY:

    SELECT pt.[imtPartID],
    CAST(DATEADD(MONTH, DATEDIFF(MONTH, 0, pt.[imtTransactionDate]), 0) AS date) AS TransMonth,
    SUM(CASE WHEN pt.[imtTransactionType] = 1 THEN 1 ELSE 0 END) AS Receipt,
    SUM(CASE WHEN pt.[imtTransactionType] = 2 THEN 1 ELSE 0 END) AS Issue,
    SUM(CASE WHEN pt.[imtTransactionType] = 3 THEN 1 ELSE 0 END) AS Adjustment
    FROM [M1_SU].[dbo].[PartTransactions] pt
    WHERE pt.[imtPartID] = '7000917'
    AND pt.[imtTransactionDate] BETWEEN '5/1/2019 3:13:52 PM' AND '10/29/2021 3:13:52 PM'
    GROUP BY pt.[imtPartID], CAST(DATEADD(MONTH, DATEDIFF(MONTH, 0, pt.[imtTransactionDate]), 0) AS date)
    ORDER BY [imtPartID], TransMonth

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • Jeff Moden wrote:

    stephen.aa wrote:

    Just one more thing... The example above spans 29 months.  Is there a good way to separate it out to place three columns for each month within the date range?

    Yes but can you post an example output just so we're sure?  What I'm concerned about is that it sounds like you want 29 sets of 3 columns all spread out horizontally for a total of more than 29*3 or 87 columns wide.  That CAN be done auto-magically but I want to make sure that's what you actually want.

    Just bumping my own response to see if you saw it.

     

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • Just one more question...

    If there are no values, thus no sums in the Pivot Query below, is there a way to have it return zero's?

    SELECT pt.[imtPartID], 
    SUM(CASE WHEN pt.[imtTransactionType] = 1 THEN 1 ELSE 0 END) AS Receipt,
    SUM(CASE WHEN pt.[imtTransactionType] = 2 THEN 1 ELSE 0 END) AS Issue,
    SUM(CASE WHEN pt.[imtTransactionType] = 3 THEN 1 ELSE 0 END) AS Adjustment
    FROM [M1_SU].[dbo].[PartTransactions] pt
    WHERE pt.[imtPartID] = 'M900156'
    AND pt.imtTransactionDate BETWEEN '8/1/2021' AND '8/31/2021'
    GROUP BY pt.[imtPartID]

     

    Steve Anderson

  • stephen.aa wrote:

    Just one more question...

    If there are no values, thus no sums in the Pivot Query below, is there a way to have it return zero's?

    SELECT pt.[imtPartID], 
    SUM(CASE WHEN pt.[imtTransactionType] = 1 THEN 1 ELSE 0 END) AS Receipt,
    SUM(CASE WHEN pt.[imtTransactionType] = 2 THEN 1 ELSE 0 END) AS Issue,
    SUM(CASE WHEN pt.[imtTransactionType] = 3 THEN 1 ELSE 0 END) AS Adjustment
    FROM [M1_SU].[dbo].[PartTransactions] pt
    WHERE pt.[imtPartID] = 'M900156'
    AND pt.imtTransactionDate BETWEEN '8/1/2021' AND '8/31/2021'
    GROUP BY pt.[imtPartID]

    Do you mean if no rows are returned from the query?

  • Below query will give you the desired output.

    SELECT

    PVT.[imtPartID],

    PVT.[1] AS Receipt,

    PVT.[2] AS Issue,

    PVT.[3] AS Adjustment

    FROM

    (

    SELECT

    [imtPartID],

    imtTransactionType

    FROM #PartTransactions

    ) AS Z

    PIVOT (COUNT(imtTransactionType) FOR imtTransactionType IN ([1],[2],[3]))AS PVT

     

     

    Attachments:
    You must be logged in to view attached files.
  • This is great.

    So now I need to add another fiend into the mix.  There must be some rules to make this happen.  Basically, I need to add the other field, imtPurchaseQuantityReceived for each transaction type.  I will study up on this further, but somehow to sum that field for each transaction type.

    Thanks

    --SELECT COUNT(*) AS COUNT FROM(
    SELECT pt.[imtPartID], pt.[imtPurchaseQuantityReceived]
    SUM(CASE WHEN pt.[imtTransactionType] = 1 THEN 1 ELSE 0 END) AS Receipt,
    SUM(CASE WHEN pt.[imtTransactionType] = 2 THEN 1 ELSE 0 END) AS Issue,
    SUM(CASE WHEN pt.[imtTransactionType] = 3 THEN 1 ELSE 0 END) AS Adjustment
    FROM [M1_SU].[dbo].[PartTransactions] pt
    WHERE pt.[imtPartID] = '3001617'
    --AND pt.imtTransactionDate BETWEEN '5/12/2021' AND '5/31/2021'
    --AND pt.imtTransactionDate BETWEEN '6/1/2021' AND '6/30/2021'
    AND pt.imtTransactionDate BETWEEN '7/1/2021' AND '7/31/2021'
    --AND pt.imtTransactionDate BETWEEN '8/1/2021' AND '8/31/2021'
    --AND pt.imtTransactionDate BETWEEN '9/1/2021' AND '9/30/2021'
    --AND pt.imtTransactionDate BETWEEN '10/1/2021' AND '10/31/2021'
    --AND pt.imtTransactionDate BETWEEN '11/1/2021' AND '11/10/2021'
    GROUP BY pt.[imtPartID], pt.[imtPurchaseQuantityReceived] --) AS COUNT

     

    Steve Anderson

Viewing 10 posts - 16 through 25 (of 25 total)

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