How to convert Rows to Columns
You are not explaining why your PIVOT query did not work for you, although it is not too hard to guess if your Response sample is representative enough of the data in that table in general. You have two sets of responses for User 2 but your query picks up only one.

As your expected output shows you want the query to return both sets, you need to teach it to distinguish between various sets of answers by the same user. One way is to use the ROW_NUMBER analytic function:

SELECT
  UserId,
  FromDate,
  ToDate,
  Project,
  Comment
FROM
  (
    SELECT
      r.UserId,
      r.Text,
      c.ColumnName,
      SetNo = ROW_NUMBER() OVER (PARTITION BY r.UserId, r.ColumnId ORDER BY r.Id)
    FROM
      dbo.Columns AS c
      INNER JOIN dbo.Response AS r ON c.Id = r.ColumnId
  ) AS derived
  PIVOT
  (
    MAX(Text)
    FOR ColumnName IN (FromDate, ToDate, Project, Comment)
  ) AS p
;


The query above makes a few assumptions:


every set of answers consists of the same set of ColumnIds;
related responses are arranged together (based on the order of Id), or, at least, if one FromDate response comes later than another FromDate response, then its related ToDate response also comes later than the ToDate response related to the previous FromDate.


If there are special cases in the real data (e.g. some sets may be incomplete), a different approach may be required.