How to make SQL columns from rows?

Question

SQL

I use SQL Server 2010.
For such a table,

create a query, the result of which will be something like this,

i.e. for each row with a pass number (Permit_Number), the time value in the rows marked enter will become the corresponding column. The same for exit.

Answer 1

I don’t know the subtleties of MS SQL, but the general essence is this:

SELECT t1.Permit_Number, t1.Pass_Time as Enter, t2.Pass_Time as Exit FROM table as t1
  LEFT JOIN table as t2 ON (
     t1.Permit_Number=t2.Permit_Number AND t2.Permit_Kind='exit'
  )
WHERE Pass_Kind='enter'

@antnjfsfds
UPD:
Didn't notice there were multiple pairs. Then you can try like this, without storage:

SELECT t1.Permit_Number, t1.Pass_Time as Enter, 
   (
    SELECT MIN(t2.Pass_Time) 
         FROM table as t2 
         WHERE t2.Pass_Kind='exit' AND t2.Pass_Time>t1.Pass_Time 
             AND t1.Permit_Number=t2.Permit_Number
   ) AS Exit
FROM table as t1
WHERE Pass_Kind='enter'

I can’t vouch for the query’s performance, there’s nowhere to check right now, but I think you understand the idea: for each entry time, select the nearest exit time with a subquery, which is greater than the entry time