CODEWITHSUNDEEP
sqlsql-servert-sql
Dub
Dub

Reputation: 33

Combine Multiple Rows And Columns Into A Single Row In SQL

I've read a few topics on combining rows in SQL and most involve using a pivot. I'm not sure a pivot will help here since I'm trying to combine multiple columns into a single row, not extracting values for the same column.

Here's what the table looks like:

OrderID   XboxLive   iTunes   XboxDate   iTunesDate   SerialNumber
9439      50.00      NULL     9/1/2018   NULL         12345
9439      NULL       82.00    NULL       9/2/2018     12345
9440      70.00      NULL     9/10/2018  NULL         12346

I would like the results to look like:

OrderID   XboxLive   iTunes   XboxDate   iTunesDate   SerialNumber
9439      50.00      82.00    9/1/2018   9/2/2018     12345
9440      70.00      NULL     9/10/2018  NULL         12346

I've tried different variations of this code below to no avail:

SELECT
  ISNULL(Xbox.OrderID, Apple.OrderID) AS OrderID,
  Xbox.XboxLive,
  Apple.iTunes,
  Xbox.XboxDate,
  Apple.iTunesDate
  ISNULL(Xbox.Serial, Apple.Serial) AS Serial
FROM SampleTable AS Xbox
FULL JOIN SampleTable AS Apple
  ON Apple.OrderID = Xbox.OrderID
  AND Apple.Serial = Xbox.Serial
 WHERE Xbox.XboxLive > 0
 OR Apple.iTunes > 0

Any help would be greatly appreciated.

Upvotes: 3

Views: 1031

Answers (2)

Barbaros Özhan
Barbaros Özhan

Reputation: 65105

Only GROUPING BY the column OrderID is enough with a proper aggregate function like AVG for SerialNumber :

SELECT OrderID, max(XboxLive) as XboxLive,
                max(iTunes) as iTunes,
                max(XboxDate) as XboxDate,
                max(iTunesDate) as iTunesDate,
                avg(SerialNumber) as SerialNumber
  FROM tab
 GROUP BY OrderID;

SQL Fiddle Demo 1

By using your style to combine the sampleTable that has been splitted, you'll again need max aggregation as in the following :

SELECT ISNULL(Xbox.OrderID, Apple.OrderID) AS OrderID,
       max(Xbox.XboxLive) as XboxLive,  
       max(Apple.iTunes) as iTunes,  
       max(Xbox.XboxDate) as XboxDate,
       max(Apple.iTunesDate) as iTunesDate,  
       max(ISNULL(Xbox.SerialNumber, Apple.SerialNumber)) AS Serial
  FROM SampleTable Xbox
  LEFT OUTER JOIN SampleTable Apple
    ON ( Apple.OrderID = Xbox.OrderID AND Apple.SerialNumber = Xbox.SerialNumber )
 GROUP BY ISNULL(Xbox.OrderID, Apple.OrderID);

SQL Fiddle Demo 2

Upvotes: 0

Ankur Patel
Ankur Patel

Reputation: 1423

Try the following:

SELECT
  OrderID,
  Max(XboxLive)
  Max(iTunes),
  Max(XboxDate),
  Max(iTunesDate)
  Serial
FROM SampleTable
Group by
  OrderID,
  Serial

Upvotes: 3

Related Questions