SQL View - Single Row Result

Question

Hi,

I've been asked to create a SQL View which contains a single row per PalletID from our Scan table.

Below is an example of the table we have. Each row is a scan against a PalletID with a ActivityRef and Scan Date of when it happened.

I need to condense this information within a view whereby I have one row per PalletID, ActivityRef1 column displays the latest scan date of when a scan activity ref of 1 is completed and the same for ActivityRef3.

Any help with this would be amazing.

Thanks

Answer 1

Hi @Testsubjec ,

Welcome to the microsoft TSQL Q&A forum!

The method provided by Erland is very clever, you can refer to it. However, there is a small mistake in his code:

CREATE VIEW Scanview AS    
SELECT PalletID,   
       MAX(CASE WHEN ActivityRef = 1 THEN ScanDate END) AS ActivityRef1,  
       MAX(CASE WHEN ActivityRef = 3 THEN ScanDate END) AS ActivityRef3  
FROM Scantable  
GROUP BY PalletID  

After the case when is ActivityRef instead of ActivityRef1.

If you have any question, please feel free to let me know.

Regards
Echo

---

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Try this:

WITH ActivityRef1 AS (
 SELECT PalletID, MAX(ScanDate) AS ActivityRef1
 FROM Scan
 WHERE ActivityRef = 1
 GROUP BY PalletID
),
ActivityRef3 AS (
 SELECT PalletID, MAX(ScanDate) AS ActivityRef3
 FROM Scan
 WHERE ActivityRef = 3
 GROUP BY PalletID
)

SELECT COALESCE(c1.PalletID, c2.PalletID) AS PalletID, 
    c1.ActivityRef1,
    c2.ActivityRef3
FROM ActivityRef1 AS c1
FULL JOIN ActivityRef3 AS c2 ON c1.PalletID = c2.PalletID;

Answer 3

Thanks for the quick response @Guoxiong , but this assumes there will be a record for ActivityRef = 1. If there isn't record I'd like to have it displayed as below. Apologies if my initial example missed this but there will always be an ActivityRef 1 record and/or ActivityRef 3 record.

Answer 4

This is a standard pivot query. No need for joins.

SELECT PalletID, 
             MAX(CASE WHEN ActivityRef1 = 1 THEN ScanDate END) AS ActivityRef1,
             MAX(CASE WHEN ActivityRef1 = 3 THEN ScanDate END) AS ActivityRef3
FROM  tbl
GROUP BY PalletID