Sql-server – SQL Server : I need to combine multiple rows into one row with different column values

This is relatively trivial to do with a correlated subquery. You can't use the COALESCE method highlighted in the blog post you mention unless you extract that to a user-defined function (or unless you only want to return one row at a time). Here is how I typically do this:

DECLARE @x TABLE 
(
  id INT, 
  row_num INT, 
  customer_code VARCHAR(32), 
  comments VARCHAR(32)
);

INSERT @x SELECT 1,1,'Dilbert','Hard'
UNION ALL SELECT 1,2,'Dilbert','Worker'
UNION ALL SELECT 2,1,'Wally','Lazy';

SELECT id, customer_code, comments = STUFF((SELECT ' ' + comments 
    FROM @x AS x2 WHERE id = x.id
     ORDER BY row_num
     FOR XML PATH('')), 1, 1, '')
FROM @x AS x
GROUP BY id, customer_code
ORDER BY id;

If you have a case where the data in comments could contain unsafe-for-XML characters (>, <, &), you should change this:

     FOR XML PATH('')), 1, 1, '')

To this more elaborate approach:

     FOR XML PATH(''), TYPE).value(N'(./text())[1]', N'varchar(max)'), 1, 1, '')

(Be sure to use the right destination data type, varchar or nvarchar, and the right length, and prefix all string literals with N if using nvarchar.)

This seems like a simple self join:

SELECT 
    enter.id,
    enter.date_ 
    enter.time AS enter_time
    leave.time AS leave_time
FROM 
    tablename AS enter
  JOIN
    tablename AS leave
  ON  enter.id = leave.id
  AND enter.date_ = leave.date_
  AND leave.EorL = 2 
WHERE
    enter.EorL = 1 ;

The query assumes there are (max) 2 rows for any id and date_.

If there are cases where people have entered without leaving, use LEFT join instead. If there are people who have left without entering, use RIGHT join. If there might be both cases, use FULL join. For RIGHT and FULL, you'll have to adjust the SELECT list, using leave.id, leave.date_ and COALESCE(enter.id, leave.id), COALESCE(enter.date_, leave.date_).