CODEWITHSUNDEEP
sqlsql-server
user1500633
user1500633

Reputation:

How can I merge two rows into one row in SQL Server

My SQL query output is like this, I need to be this in one row.

 iProductM  iProductO   UnitCostM   UnitCostO
    7065    NULL         30.67      NULL
    NULL    7065         NULL       29.78

Required output:

iProductM   iProductO   UnitCostM   UnitCostO
        7065     7065        30.67      29.78

My query is as follows:

SELECT 
    coalesce(iProductM, iProductO) as P,
    coalesce(UnitCostM, UnitCostO) as U  
FROM 
    ViewForCostAll 
WHERE
    iProductO = 7065 OR iProductM = 7065

But my output is still in two rows:

P       U
7065    30.67
7065    29.78

Can anyone help me?

Upvotes: 0

Views: 1176

Answers (2)

Freddie Bell
Freddie Bell

Reputation: 2287

A join is what you do to combine rows. Once the join is done, you can compare values within the (internal resulting) row, and output selected results. This query only works for the 2 rows you asked about. If your data has more rows, let's see them and make a query that works for your actual data.

SELECT 
    isNull(V1.iProductM, V2.iProductM) as iProductM,
    isNull(V1.iProductO, V2.iProductO) as iProductO,
    isNull(V1.UnitCostM, V2.UnitCostM) as UnitCostM,  
    isNull(V1.UnitCostO, V2.UnitCostO) as UnitCostO  
FROM 
   ViewForCostAll V1
LEFT JOIN
   ViewforCostAll V2 on (V2.iProductM is null)
WHERE
    V1.iProductO = 7065

Upvotes: 0

StanislavL
StanislavL

Reputation: 57381

SELECT DISTINCT
    coalesce(iProductM, iProductO) as iProductM,
    coalesce(iProductO, iProductM) as iProductM,
    coalesce(UnitCostM, UnitCostO) as UnitCostM,  
    coalesce(UnitCostO, UnitCostM) as UnitCostO  
FROM 
    ViewForCostAll 
WHERE
    iProductO = 7065 OR iProductM = 7065

You can use one column if another is null and then use DISTICNT to remove duplicates

Upvotes: 1

Related Questions