Sql-server – Selection of first and last in non grouped item in SQL Server

group bysql server

Here's my table structure

Id  ToolName  ToolKey   ToolStatus
-----------------------------------    
1    XXXX      123      Running
2    XXXX      123      Completed
3    yyyy      134      Running
4    yyyy      134      Completed

I have grouped the above statement by toolname and toolkey.Here's my query

SELECT 
    TOOLNAME, ToolKey
FROM 
    TOOLS
GROUP BY 
    TOOLS.TOOLNAME, TOOLS.ToolKey

I need to have columns ToolStatus1 with value Running and ToolStatus2 with value Completed. If not completed means it should have null.

But in SQL if I add toolstatus in grouping, grouping will not be done.

In simple what I expect is like First() and last() as I will be having only two rows.

Let me know if unclear

Best Answer

Here are a couple of options:

   SELECT TOOLNAME, 
           TOOLKEY, 
           MAX(CASE WHEN Toolstatus = 'Running' THEN 'Running' END) Toolstatus1, 
           MAX(CASE WHEN Toolstatus = 'Completed' THEN 'Completed' END) Toolstatus2
    FROM TOOLS
    GROUP BY TOOLNAME, 
             TOOLKEY

It is eliminating nulls with the aggregate MAX() function in this code, if that's a concern, you may want this:

SELECT TOOLNAME, 
       TOOLKEY, 
       [Running] Toolstatus1,
       [Completed] Toolstatus2
FROM (SELECT TOOLNAME, 
             TOOLKEY,
             TOOLSTATUS
      FROM TOOLS) AS src
PIVOT
(
MAX(TOOLSTATUS)
FOR TOOLSTATUS IN ([RUNNING], [COMPLETED])
) AS pvt