CODEWITHSUNDEEP
sqlsql-server
Naveen
Naveen

Reputation: 701

How to select two rows into a single row output

The select statement looks like this right now

The select statement looks like this right now

Here i got the output in 2 lines for the same stg_edi835_id ,I want to select the results in a single line for that stg_edi835_id.

Output should look like this

enter image description here

Can some please help me in doing this

Thanks in advance..

Upvotes: 0

Views: 121

Answers (3)

Santhosh
Santhosh

Reputation: 1791

SELECT STG_EDI835_PLB_ID, STG_EDI835_ID, ADJUSTMENTREASONCODE1, ADJUSTMENTIDENTIFIER1, SUM(NTVE_ADJUSTMENTAMOUNT1_T1+NTVE_ADJUSTMENTAMOUNT1_T2) AS ADJUSTMENTAMOUNT1,
SUM(PTVE_ADJUSTMENTAMOUNT1_T1+PTVE_ADJUSTMENTAMOUNT1_T2) AS ADJUSTMENTAMOUNT2, ADJUSTMENTREASONCODE2, ADJUSTMENTIDENTIFIER2
FROM
(
    SELECT T1.STG_EDI835_PLB_ID , T2.STG_EDI835_ID, T1.ADJUSTMENTREASONCODE1, T1.ADJUSTMENTIDENTIFIER1, 
    (CASE WHEN T1.ADJUSTMENTAMOUNT1 < 0 THEN T1.ADJUSTMENTAMOUNT1 ELSE 0 END) AS NTVE_ADJUSTMENTAMOUNT1_T1,
    (CASE WHEN T2.ADJUSTMENTAMOUNT1 < 0 THEN T2.ADJUSTMENTAMOUNT1 ELSE 0 END) AS NTVE_ADJUSTMENTAMOUNT1_T2,
    (CASE WHEN T1.ADJUSTMENTAMOUNT1 >= 0 THEN T1.ADJUSTMENTAMOUNT1 ELSE 0 END) AS PTVE_ADJUSTMENTAMOUNT1_T1,
    (CASE WHEN T2.ADJUSTMENTAMOUNT1 >= 0 THEN T2.ADJUSTMENTAMOUNT1 ELSE 0 END) AS PTVE_ADJUSTMENTAMOUNT1_T2,
    COALESCE(T2.ADJUSTMENTREASONCODE1, 'NULL') AS ADJUSTMENTREASONCODE2, COALESCE(T2.ADJUSTMENTIDENTIFIER1, NULL) AS ADJUSTMENTIDENTIFIER2
    FROM TABLE1 AS T1
    INNER JOIN TABLES T2
    ON T2.STG_EDI835_ID = T1.STG_EDI835_ID
    AND T2.STG_EDI835_PLB_ID = T1.STG_EDI835_PLB_ID 
) A 
GROUP BY STG_EDI835_PLB_ID, STG_EDI835_ID, ADJUSTMENTREASONCODE1, ADJUSTMENTIDENTIFIER1, ADJUSTMENTREASONCODE2, ADJUSTMENTIDENTIFIER2

Upvotes: 1

Chains
Chains

Reputation: 13157

Your question is somewhat incomplete (you should show your desired output), however, here is a sample of what you could do:

  • get rid of all unique values you don't need. (column 1, containing ID's, etc.)
  • Use aggregate functions on the rest.

Example:

Select
   --column 1 removed
   MAX(column2) as ID,
   MAX(column3) as RefID,
   --column 4 removed
   --column 5 removed
   --column 6 removed
   SUM(column7) as Ad1,
   --column 8 removed
   --column 9 removed
   SUM(column10) as Ad2
From
   table

Upvotes: 1

mgaert
mgaert

Reputation: 2388

Try something like

WITH DATA As (
select 697 as Stg_EDI835_Id, -87.75 as AdjustmentAmount1 union
select 697, -4.64 union
select 612, -6.39 union
select 612, 60.75
)
select SUM(AdjustmentAmount1) AS AdjustmentAmount1, 0 AS adjustmentamount2 FROM DATA GROUP BY Stg_EDI835_Id HAVING SUM(AdjustmentAmount1) <= 0 UNION
select 0, SUM(AdjustmentAmount1)FROM DATA GROUP BY Stg_EDI835_Id HAVING SUM(AdjustmentAmount1) > 0

Output is

AdjustmentAmount1 | Adjustmentamount2
    -92.39        |      0.00
      0.00        |     54.36

Upvotes: 0

Related Questions