join with empty rows

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

Answer 1

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.  

Answer 2

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.

Answer 3

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.

Answer 4

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

Answer 5

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

Answer 6

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