question

Testsubjec avatar image
0 Votes"
Testsubjec asked ErlandSommarskog commented

SQL View - Single Row Result

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.

99484-image.png


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.

99548-image.png

Any help with this would be amazing.

Thanks

sql-server-generalsql-server-transact-sql
image.png (15.6 KiB)
image.png (5.1 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered ErlandSommarskog commented

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.


· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks for the correct, Echo.

This is when happens when you post data in images instead of posting CREATE TABLE + INSERT statements. The latter can easily be copied to a query window, permitting us to develop a tested query. This is not possible with images.

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Testsubjec avatar image
0 Votes"
Testsubjec answered Testsubjec edited

Thanks for the quick response @GuoxiongYuan-7218, 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.

99622-image.png



image.png (5.1 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I changed LEFT JOIN to FULL JOIN and c1.PalletID to COALESCE(c1.PalletID, c2.PalletID) AS PalletID. Try it.

0 Votes 0 ·
GuoxiongYuan-7218 avatar image
0 Votes"
GuoxiongYuan-7218 answered GuoxiongYuan-7218 edited

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;
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.