SQL View - Single Row Result

Testsubjec 21 Reputation points


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.


SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,312 questions
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,522 questions
0 comments No comments
{count} votes

Accepted answer
  1. EchoLiu-MSFT 14,571 Reputation points

    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.


    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.

3 additional answers

Sort by: Most helpful
  1. Guoxiong 8,126 Reputation points

    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, 
    FROM ActivityRef1 AS c1
    FULL JOIN ActivityRef3 AS c2 ON c1.PalletID = c2.PalletID;
    0 comments No comments

  2. Testsubjec 21 Reputation points

    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.


  3. Erland Sommarskog 99,296 Reputation points MVP

    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
    0 comments No comments