locked
join with empty rows RRS feed

  • Question

  • User992700523 posted

    Asalam Alaikum (Greetings)!

    I have record in these form:

    Item ExpenseID Rate Charge ExpenseID Rate Charge ExpenseID Rate Charge
    1038 12 0 8750 NULL NULL NULL NULL NULL NULL
    1038 NULL NULL NULL 16 0 255268 NULL NULL NULL
    1038 NULL NULL NULL NULL NULL NULL 28 0 2156307

    I want to change the result into a single line form as:

    Item ExpenseID Rate Charge ExpenseID Rate Charge ExpenseID Rate Charge
    1038 12 0 8750 16 0 255268 28 0 2156307

    Kindly help!

    Thank you,

    Regards,
    Muhammad Naveed Haroon

    Tuesday, September 4, 2018 8:41 AM

Answers

All replies

  • User475983607 posted

    There is no easy way implement this requirement due to all the combinations of possible record formats.  I recommend fixing the design to update a single record, 1038 in this case, rather than adding new records with partial information.  

    Tuesday, September 4, 2018 12:00 PM
  • User992700523 posted

    Thank you for reply.

    There is no design like this i get this result as per the requirement. The original quires have different result like

    1088    Shampoo     expense1  100
    1088    Shampoo     expense2  200
    1088    Shampoo     expense3  300

    i try to change it on that way so get i got easily result in reports.

    Tuesday, September 4, 2018 12:31 PM
  • User475983607 posted

    Thank you for reply.

    There is no design like this i get this result as per the requirement. The original quires have different result like

    1088    Shampoo     expense1  100
    1088    Shampoo     expense2  200
    1088    Shampoo     expense3  300

    i try to change it on that way so get i got easily result in reports.

    The question is missing to much information to guess the database schema or how the result set is populated.

    Tuesday, September 4, 2018 1:21 PM
  • User992700523 posted

    Thank you! The information i wrote the query populate the data as:

    1088  Shampoo   expense1  100
    1088  Shampoo   expense2  110
    1088  Shampoo   expense3  190
    1088  Shampoo   expense4   250
    1088  Conditioner expense1  200
    1088  Conditioner expense2  174
    1088  Conditioner expense4  190

    i want the result in the form i provide such as:

    1088  Shampoo  expense1  100 Conditioner expense1 200
    1088  Shampoo  expense2  110 Conditioner expense2 174
    1088  Shampoo  expense3  190 Conditioner expense3  0
    1088  Shampoo  expense4  250 Conditioner expense4 190

    is this enough to resolve or should i share more information to you.

    Thanks & Regards,

    Muhammad Naveed Haroon

    Wednesday, September 5, 2018 10:20 AM
  • User992700523 posted

    Thank you, but i didn't work according to the result i mentioned in my questions. Kindly re check the first and the 3rd post and reconsider the situation and kindly help.

    Thank you,

    Regards,
    Muhammad Naveed Haroon

    Tuesday, September 11, 2018 1:10 PM
  • User1520731567 posted

    Hi MuhammadNaveedHaroon,

    According to your requirement, I suggest you could use Group By in sql.

    For example:

    More details,you could refer to:

    https://www.codeproject.com/Questions/1205130/How-can-I-merge-multiple-rows-with-same-ID-into-on

    Best Regards.

    Yuki Tao

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, September 12, 2018 8:43 AM