Post reply

SQL Server Partial Unpivot for defined days columns, repeat rows by column

  • September 3, 2019 at 12:47 pm

    Go to Answer

    #3676839

    Hello,

    I need to process stock information by day, but the database stores the info per week, using a column for each day.

    I've tryed using unpivot but I need to repeat first columns and create new records per day. I am not finding the right way to do it.

    I am attaching an image with current data and expected output.

    Let me know if not clear and I will give further details.

    Thank you!

  • September 3, 2019 at 1:21 pm

    #3676871

    I could not see any attachment.

    However two samples that may help

    SELECTID, DayNo, DayValue
    FROM(
    SELECTID, Val1, Val2, Val3, Val4, Val5, Val6, Val7
    FROM
    ) p (ID, [1], [2], [3], [4], [5], [6], [7])
    UNPIVOT(
    DayValue FOR DayNo IN ([1], [2], [3], [4], [5], [6], [7])  
    ) a;  
    SELECTt.ID,d.DayNo,v.DayValue
    FROM t
    CROSS JOIN (
    VALUES (1),(2),(3),(4),(5),(6),(7)
    ) d (DayNo)
    CROSS APPLY (
    SELECT CASE d.DayNo
    WHEN 1 THEN t.Val1
    WHEN 2 THEN t.Val2
    WHEN 3 THEN t.Val3
    WHEN 4 THEN t.Val4
    WHEN 5 THEN t.Val5
    WHEN 6 THEN t.Val6
    ELSE t.Val7
    END
    ) v (DayValue);

    Far away is close at hand in the images of elsewhere.
    Anon.

  • September 3, 2019 at 1:39 pm

    #3676873

    MatiOli wrote:

    Hello,

    I need to process stock information by day, but the database stores the info per week, using a column for each day.

    I've tryed using unpivot but I need to repeat first columns and create new records per day. I am not finding the right way to do it.

    I am attaching an image with current data and expected output.

    Let me know if not clear and I will give further details.

    Thank you!

    No image was attached.

    If you are looking to get a working solution, it will be necessary for you to post more than just an image, we'll need some sample code (CREATE TABLE #t ... and INSERT #t ...) to set up test data, along with desired results based on that test data.

  • September 3, 2019 at 2:03 pm

    #3676875

    Hello Guys, Thank yoy for your quick response.

    Sorry, it is my first post. I think my network from work doesnt allow uploading and maybe it was blocked.

    Let me try using BBCodes. I hope it works.

    Source:

    COUNTRY_CODECOMPANY_CODEITEM_NBRDepartmentWeekSUN_QTYMON_QTYTUE_QTYWED_QTYTHU_QTYFRI_QTYSAT_QTY
    USING5555191173119191943414022

    Expected output:

    COUNTRY_CODECOMPANY_CODEITEM_NBRDepartmentWeekDayQTY
    USING55551911731SUN19
    USING55551911731MON19
    USING55551911731TUE19
    USING55551911731WED43
    USING55551911731THU41
    USING55551911731FRI40
    USING55551911731SAT22

     

     

    Attachments:
    You must be logged in to view attached files.

  • September 3, 2019 at 2:25 pm

    #3676881

    Here's a quick solution. There are more elegant ways of getting there, but I'm a little short of time.

    DROP TABLE IF EXISTS #t1;

    CREATE TABLE #t1
    (
        CountryCode CHAR(2)
       ,CompanyCode CHAR(3)
       ,ItemNum INT
       ,Department SMALLINT
       ,Wk SMALLINT
       ,SunQty SMALLINT
       ,MonQty SMALLINT
       ,TueQty SMALLINT
       ,WedQty SMALLINT
       ,ThuQty SMALLINT
       ,FriQty SMALLINT
       ,SatQty SMALLINT
    );

    INSERT #t1
    (
        CountryCode
       ,CompanyCode
       ,ItemNum
       ,Department
       ,Wk
       ,SunQty
       ,MonQty
       ,TueQty
       ,WedQty
       ,ThuQty
       ,FriQty
       ,SatQty
    )
    VALUES
    ('US', 'ING', 5555, 19, 11731, 19, 19, 19, 43, 41, 40, 22);

    WITH Dys (Day)
    AS (SELECT 'SUN'
        UNION ALL
        SELECT 'MON'
        UNION ALL
        SELECT 'TUE'
        UNION ALL
        SELECT 'WED'
        UNION ALL
        SELECT 'THU'
        UNION ALL
        SELECT 'FRI'
        UNION ALL
        SELECT 'SAT')
    SELECT t.CountryCode
          ,t.CompanyCode
          ,t.ItemNum
          ,t.Department
          ,t.Wk
          ,Dys.Day
          ,CASE Dys.Day
               WHEN 'SUN' THEN
                   t.SunQty
               WHEN 'MON' THEN
                   t.MonQty
    --etc etc
           END
    FROM #t1 t
        CROSS JOIN Dys;

  • September 3, 2019 at 2:55 pm

    #3676903

    Thank you Phil! Nailed it.

    Let's see how it works with the whole solution and extended data.

    CountryCode CompanyCode ItemNum Department Wk Day CANT

    US ING 5555 19 11731 SUN 19

    US ING 5555 19 11731 MON 19

    US ING 5555 19 11731 TUE 19

    US ING 5555 19 11731 WED 43

    US ING 5555 19 11731 THU 41

    US ING 5555 19 11731 FRI 40

    US ING 5555 19 11731 SAT 22

    If any other solution to share, I will appreciate it. Just for the records.

    Cheers!

     

     

  • September 3, 2019 at 2:57 pm

    #3676904

    Hello David,

    I am sorry I didn´t send all the info in the right way. Thank you for your response.

    If you want to share your solution now that we got the whole description, be welcome.

    Regards

  • September 3, 2019 at 3:04 pm

    #3676905

    You would fair much better if you provided readily consumable data.  Help us help you... See the first link in my signature line below for one way to create "Readily Consumable" data.

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

  • September 3, 2019 at 3:25 pm

    #3676909

    Indeed. Next posts will be more accurated.

    Thanks

  • September 3, 2019 at 3:26 pm

    #3676910

    David Burrows wrote:

    I could not see any attachment.

    However two samples that may help

    SELECTID, DayNo, DayValue
    FROM(
    SELECTID, Val1, Val2, Val3, Val4, Val5, Val6, Val7
    FROM
    ) p (ID, [1], [2], [3], [4], [5], [6], [7])
    UNPIVOT(
    DayValue FOR DayNo IN ([1], [2], [3], [4], [5], [6], [7])  
    ) a;

     

    SELECTt.ID,d.DayNo,v.DayValue
    FROM t
    CROSS JOIN (
    VALUES (1),(2),(3),(4),(5),(6),(7)
    ) d (DayNo)
    CROSS APPLY (
    SELECT CASE d.DayNo
    WHEN 1 THEN t.Val1
    WHEN 2 THEN t.Val2
    WHEN 3 THEN t.Val3
    WHEN 4 THEN t.Val4
    WHEN 5 THEN t.Val5
    WHEN 6 THEN t.Val6
    ELSE t.Val7
    END
    ) v (DayValue);

    Hello David,

    I am sorry I didn´t send all the info in the right way. Thank you for your response.

    If you want to share your solution now that we got the whole description, be welcome.

    Regards

  • September 3, 2019 at 4:05 pm

    Answer

    #3676940

    Phil's solution is what I would have done.

    Just for completeness

    SELECTCOUNTRY_CODE,COMPANY_CODE,ITEM_NBR,Department,[Week],[Day],QTY
    FROM(
    SELECTCOUNTRY_CODE,COMPANY_CODE,ITEM_NBR,Department,[Week],
    SUN_QTY,MON_QTY,TUE_QTY,WED_QTY,THU_QTY,FRI_QTY,SAT_QTY
    FROM
    ) p (COUNTRY_CODE,COMPANY_CODE,ITEM_NBR,Department,[Week],
    [SUN],[MON],[TUE],[WED],[THU],[FRI],[SAT])
    UNPIVOT(
    [QTY] FOR [Day] IN ([SUN],[MON],[TUE],[WED],[THU],[FRI],[SAT])
    ) a;
    SELECTt.COUNTRY_CODE,t.COMPANY_CODE,t.ITEM_NBR,t.Department,t.[Week],d.[Day],q.QTY
    FROM t
    CROSS JOIN (
    VALUES ('SUN'),('MON'),('TUE'),('WED'),('THU'),('FRI'),('SAT')
    ) d ([Day])
    CROSS APPLY (
    SELECT CASE d.[Day]
    WHEN 'SUN' THEN t.SUN_QTY
    WHEN 'MON' THEN t.MON_QTY
    WHEN 'TUE' THEN t.TUE_QTY
    WHEN 'WED' THEN t.WED_QTY
    WHEN 'THU' THEN t.THU_QTY
    WHEN 'FRI' THEN t.FRI_QTY
    ELSE t.SAT_QTY
    END
    ) q (QTY);

    Far away is close at hand in the images of elsewhere.
    Anon.

  • September 3, 2019 at 6:04 pm

    #3676971

    All excelent responses. I have tested the three answers and work.

    Thank you all!

  • September 4, 2019 at 6:23 pm

    #3677329

    David Burrows wrote:

    SELECTt.COUNTRY_CODE,t.COMPANY_CODE,t.ITEM_NBR,t.Department,t.[Week],d.[Day],q.QTY
    FROM t
    CROSS JOIN (
    VALUES ('SUN'),('MON'),('TUE'),('WED'),('THU'),('FRI'),('SAT')
    ) d ([Day])
    CROSS APPLY (
    SELECT CASE d.[Day]
    WHEN 'SUN' THEN t.SUN_QTY
    WHEN 'MON' THEN t.MON_QTY
    WHEN 'TUE' THEN t.TUE_QTY
    WHEN 'WED' THEN t.WED_QTY
    WHEN 'THU' THEN t.THU_QTY
    WHEN 'FRI' THEN t.FRI_QTY
    ELSE t.SAT_QTY
    END
    ) q (QTY);

    This can be greatly simplified.  There's no reason to do this in two steps (CROSS JOIN then CROSS APPLY).

    SELECTt.COUNTRY_CODE,t.COMPANY_CODE,t.ITEM_NBR,t.Department,t.[Week],d.[Day],q.QTY
    FROM t
    CROSS APPLY (
    VALUES (
    ('SUN', t.SUN_QTY)
    ,('MON', t.MON_QTY)
    ,('TUE', t.TUE_QTY)
    ,('WED', t.WED_QTY)
    ,('THU', t.THU_QTY)
    ,('FRI', t.FRI_QTY)
    ,('SAT', t.SAT_QTY)
    ) q (dt, qty);

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • September 5, 2019 at 8:12 am

    #3677531

    drew.allen wrote:

    David Burrows wrote:

    SELECTt.COUNTRY_CODE,t.COMPANY_CODE,t.ITEM_NBR,t.Department,t.[Week],d.[Day],q.QTY
    FROM t
    CROSS JOIN (
    VALUES ('SUN'),('MON'),('TUE'),('WED'),('THU'),('FRI'),('SAT')
    ) d ([Day])
    CROSS APPLY (
    SELECT CASE d.[Day]
    WHEN 'SUN' THEN t.SUN_QTY
    WHEN 'MON' THEN t.MON_QTY
    WHEN 'TUE' THEN t.TUE_QTY
    WHEN 'WED' THEN t.WED_QTY
    WHEN 'THU' THEN t.THU_QTY
    WHEN 'FRI' THEN t.FRI_QTY
    ELSE t.SAT_QTY
    END
    ) q (QTY);

    This can be greatly simplified.  There's no reason to do this in two steps (CROSS JOIN then CROSS APPLY).

    SELECTt.COUNTRY_CODE,t.COMPANY_CODE,t.ITEM_NBR,t.Department,t.[Week],d.[Day],q.QTY
    FROM t
    CROSS APPLY (
    VALUES (
    ('SUN', t.SUN_QTY)
    ,('MON', t.MON_QTY)
    ,('TUE', t.TUE_QTY)
    ,('WED', t.WED_QTY)
    ,('THU', t.THU_QTY)
    ,('FRI', t.FRI_QTY)
    ,('SAT', t.SAT_QTY)
    ) q (dt, qty);

    Drew

    Nice 🙂

    Forgot about that as I have done that several times before doh!

    btw there is a syntax error in your code an extra parenthesis after VALUES

    Far away is close at hand in the images of elsewhere.
    Anon.

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

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