Get two rows in a single row

Question

0.00/5 (No votes)

See more:

Hi,
I have a table Report as below

HTML

Group_Display_Name	Equipment_Display_Name	Parameter Name	Required Pounds	Actual Pounds
SECOND Group	LIME	        AMOUNT_ADDED	0	7245.495605
SECOND Group	LIME	        CHARGE_AMOUNT	7263	0
SECOND Group	METHANOL	AMOUNT_ADDED	0	5714.498047
SECOND Group	METHANOL	CHARGE_AMOUNT	0	0
THIRD Group	LIME	        AMOUNT_ADDED	0	3603.398926
THIRD Group	LIME	        CHARGE_AMOUNT	3625	0
THIRD Group	XYLENE	        AMOUNT_ADDED	0	10177.30566
THIRD Group	XYLENE	        CHARGE_AMOUNT	10070	0

Every group has an equipment with Parameter as Charge Amount and Amount Added.
I want to show these two lines as one line like,

HTML

Second Group    LIME    7236    7245.495605
Second Group   Methanol    0   5714.498047

Column Parameter Name, I do not want to display.

What I have tried:

I tried to use CASE but could not get what I want.

Posted 3-Jun-18 19:02pm

Lokesh Zende

Updated 4-Jun-18 0:53am

Add a Solution

2 solutions

Solution 1

Please try:

SQL

SELECT Group_display_name,Equipment_Display_name,
    SUM(CASE PARAMETER_NAME WHEN 'CHARGE_AMOUNT' THEN required_pounds ELSE 0 END) AS c1,
    SUM(CASE PARAMETER_NAME WHEN 'CHARGE_AMOUNT' THEN 0 ELSE actual_pounds END) AS c2
    FROM data
GROUP BY Group_display_name,Equipment_Display_name
ORDER  by Group_display_name,Equipment_Display_name

Posted 3-Jun-18 20:53pm

Member 7870345

Comments

Maciej Los 4-Jun-18 6:55am

CASE WHEN is redundant... See my answer.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2018-06-04T00:55:00

Solution 2

Try this:

SQL

SELECT Group_Display_Name, Equipment_Display_Name, SUM([Required Pounds]) AS RP, SUM([Actual Pounds]) AS AP
FROM YOUR_TABLE
GROUP BY Group_Display_Name, Equipment_Display_Name

Good luck!

Posted 4-Jun-18 0:55am

Maciej Los