Retrieving specific column data from SQL sub-query

My goal is to pull out the values shown in red in the picture.

I used this query to pull the data together. The rows are associated via ActionID and filtered with the ‘AND’ conditions shown.

SELECT  [lclTime],[Descript],[Event],[PValue],[Phase],[ActionID]
FROM TableA
WHERE uniqueid = 5742 
    AND ActionID <> 0
    AND LEN(Phase) = 0
    AND [ActionID] in
    (
        SELECT [ActionID] FROM TableA
        WHERE uniqueid = 5742 
        AND ActionID <> 0
        AND LEN(Phase) = 0
        GROUP BY [ActionID] 
        HAVING COUNT(*) > 2
    )

I thought I could treat the output from above, as the input to another query, to allow me to choose the values I need to extract. But was unsuccesful. I also thought that maybe PIVOT could be used to pull out the values in the columns… but I need values from multiple columns and couldn’t get that syntax to even work.

Can this be done without temp tables? I don’t think the 3rd party app, that will execute this code, plays nicely with temp tables.

Thanks in advance.

UPDATE: I should have clarified what I’m trying to achieve for a result set. For each set described by the subquery, I’m trying to extract a single record per unique ActionID. And the values with the ActionID would include the value of lclTime, Descript and Pvalue when Event=Successful Signoff, and the value of PValue when Event=State Command.

Answer

I suspect that you want conditional aggregation, to bring the values of events “State Command” and “Successful Signoff” for each actionid that satisfies the filter conditions and that have at least 3 occurences.

If so, you can use a window count for filtering, and then conditional aggregation to pivot:

select actionid, 
    max(case when event = 'State Command'      then pvalue  end) command_value,
    max(case when event = 'State Command'      then lcltime end) command_lcltime,
    max(case when event = 'Successful Signoff' then pvalue  end) signoff_value,
    max(case when event = 'Successful Signoff' then lcltime end) signoff_lcltime,
from (
    select a.*, count(*) over(partition by actionid) cnt
    from tablea
    where uniqueid = 5742 and actionid <> 0 and len(phase) = 0
) t
where cnt > 2
group by actionid