SQL View - Single Row Result

Testsubjec 21 Reputation points
2021-05-25T17:21:46.547+00:00

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
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
Transact-SQL
Transact-SQL
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
    2021-05-26T02:30:01.727+00:00

    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.


3 additional answers

Sort by: Most helpful
  1. Guoxiong 8,126 Reputation points
    2021-05-25T18:07:20.483+00:00

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

  2. Testsubjec 21 Reputation points
    2021-05-25T20:45:38.07+00:00

    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.

    99622-image.png


  3. Erland Sommarskog 99,296 Reputation points MVP
    2021-05-25T21:53:29.69+00:00

    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