Post reply

Can I pivot?

  • November 28, 2012 at 8:40 am

    #266633

    Hi,

    I'd like to have a go at pivoting this small dataset to start with before applying to a larger set of data.

    Can it be done?

    This is the starting result set

    stockcode, order_count, qty_required, location, release

    231756, 9, 12, 1, N

    231756, 2, 2, 1, Y

    231756, 1, 1, 2, Y

    231756, 2, 5, 2, N

    I'd like it to look like this

    stockcode, location, order_count_ALL, order_count_release_Y, qty_required_ALL, qty_required_release_Y

    231756, 1, 11, 2, 14, 2

    231756, 2, 3, 1, 6, 1

    So I want to keep the stockcode and location, and pivot the data based on the release field.

    Sum up the quantity and order counts for 2 of the columns, and return the values for the Y records for the other 2.

    Might be overly complicated in a pivot I don't know

    Thanks

  • November 28, 2012 at 8:51 am

    #1563223

    Forgot to say the locations can only be between 1 and 8, though only 2 are shown here

  • November 28, 2012 at 8:56 am

    #1563227

    You're not really pivoting, you just need to add conditions to a SUM.

    You're grouping by stockcode and location and suming orders and quantities.

    Just think about it and try something.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • November 28, 2012 at 9:01 am

    #1563230

    --== SAMPLE DATA ==--

    SELECT stockcode, order_count, qty_required, location, release

    INTO #yourSampleData

    FROM (VALUES(231756, 9, 12, 1, 'N'),(231756, 2, 2, 1, 'Y'),

    (231756, 1, 1, 2, 'Y'),(231756, 2, 5, 2, 'N')

    )a(stockcode, order_count, qty_required, location, release);

    --== ACTUAL QUERY ==--

    SELECT stockcode, location,

    order_count_ALL = SUM(order_count),

    order_count_release_Y = SUM(CASE WHEN release = 'Y' THEN order_count ELSE 0 END),

    qty_required_ALL = SUM(qty_required),

    qty_required_release_Y = SUM(CASE WHEN release = 'Y' THEN qty_required ELSE 0 END)

    FROM #yourSampleData

    GROUP BY stockcode, location;

    /* RESULTS

    stockcode location order_count_ALL order_count_release_Y qty_required_ALL qty_required_release_Y

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

    231756 1 11 2 14 2

    231756 2 3 1 6 1

    */

    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • November 28, 2012 at 9:01 am

    #1563231

    mike.littlewd (11/28/2012)

    Hi,

    I'd like to have a go at pivoting this small dataset to start with before applying to a larger set of data.

    Can it be done?

    This is the starting result set

    stockcode, order_count, qty_required, location, release

    231756, 9, 12, 1, N

    231756, 2, 2, 1, Y

    231756, 1, 1, 2, Y

    231756, 2, 5, 2, N

    I'd like it to look like this

    stockcode, location, order_count_ALL, order_count_release_Y, qty_required_ALL, qty_required_release_Y

    231756, 1, 11, 2, 14, 2

    231756, 2, 3, 1, 6, 1

    So I want to keep the stockcode and location, and pivot the data based on the release field.

    Sum up the quantity and order counts for 2 of the columns, and return the values for the Y records for the other 2.

    Might be overly complicated in a pivot I don't know

    Thanks

    Hi and welcome to SSC. What you described and posted is not a pivot. What I don't understand though is the how you derive the value in your desired output. It doesn't seem to match your sample data.

    In order to give you the best help I would recommend reading the article at the first link in my signature for best practices when posting questions.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • November 28, 2012 at 9:24 am

    #1563258

    Ah thanks, I was originally trying to do something like that but it kept asking for the release field to be in the group by part of the query.

    I assume this was because I was doing something like

    case when release = 'Y' then sum() else 0 end

    Thanks for your help

Viewing 6 posts - 1 through 6 (of 6 total)

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