How to convert Rows to Columns in Sql

Question

I've a table Columns

and a second table Response in which all data is saved.

Now I want to create a SQL View in which the result should be like this

I tried using pivot

select UserId ,FromDate, ToDate, Project, Comment
from
(
  select R.UserId ,R.Text , C.ColumnName
  from [Columns] C
  INNER JOIN Response R ON C.Id=R.ColumnId
) d
pivot
(
  max(Text)
  for ColumnName in (FromDate, ToDate, Project, Comment)
) piv;

but that didn't worked for me, I also referred this Efficiently convert rows to columns in sql server but was not able to implement it. Any ideas how to achieve the same in SQL View?

Scripts for Tables:

CREATE TABLE [dbo].[Columns](
    [Id] [bigint] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](1000) NULL,
    [IsActive] [bit] NULL,
 CONSTRAINT [PK_Columns] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

insert into [Columns] values('FromDate',1)
insert into [Columns] values('ToDate',1)
insert into [Columns] values('Project',1)
insert into [Columns] values('Comment',1)

CREATE TABLE [dbo].[Response](
    [Id] [bigint] IDENTITY(1,1) NOT NULL,
    [UserId] [bigint]  NOT NULL,
    [ColumnId] [bigint]  NOT NULL,
    [Text] [nvarchar](max) NULL,
    [IsActive] [bit] NULL,
    CONSTRAINT [PK_Response] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
insert into [Response] values(1,1,'1/1/2012',1)
insert into [Response] values(1,2,'1/2/2012',1)
insert into [Response] values(1,3,'p1',1)
insert into [Response] values(1,4,'c1',1)
insert into [Response] values(2,1,'1/1/2013',1)
insert into [Response] values(2,2,'1/2/2013',1)
insert into [Response] values(2,3,'p2',1)
insert into [Response] values(2,4,'c2',1)
insert into [Response] values(2,1,'1/1/2014',1)
insert into [Response] values(2,2,'1/2/2014',1)
insert into [Response] values(2,3,'p3',1)
insert into [Response] values(2,4,'c3',1)
insert into [Response] values(3,1,'1/1/2015',1)
insert into [Response] values(3,2,'1/2/2015',1)
insert into [Response] values(3,3,'p4',1)
insert into [Response] values(3,4,'c4',1)

Answer 1

Honestly, if the column types aren't going to change, or you only need a subset of them, you could just filter them out and then join on them rather than write a pivot. I wrote it using a cte, but they could just as easily be sub-queries:

;with fd as
(
    select
        UserID,
        [Text] as FromDate,
        row_number() over (partition by userID order by ID) as DEDUP
    from response
    where ColumnID = 1
),
td as
(
    select
        UserID,
        [Text] as ToDate,
        row_number() over (partition by userID order by ID) as DEDUP
    from response
    where ColumnID = 2
),
p as
(
    select
        UserID,
        [Text] as Project,
        row_number() over (partition by userID order by ID) as DEDUP
    from response
    where ColumnID = 3
),
c as
(
    select
        UserID,
        [Text] as Comment,
        row_number() over (partition by userID order by ID) as DEDUP
    from response
    where ColumnID = 4
)
select
    fd.*,
    td.ToDate,
    p.Project,
    c.Comment
from fd
    inner join td
        on fd.UserId = td.UserId
            and fd.DEDUP = td.DEDUP
    inner join p
        on fd.UserId = p.UserId
            and fd.DEDUP = p.DEDUP
    inner join c
        on fd.UserId = c.UserId
            and fd.DEDUP = c.DEDUP

Answer 2

You can query like this

;with cte as 
(
    select r.*, 
    c.name 
    from Response r 
        inner join Columns c
            on r.columnid = c.id
) 
select 
    Userid, 
    max([FromDate]) as [FromDate],
    max([ToDate]) as [ToDate],
    max([Project]) as [Project],
    max([Comment]) as [Comment] 
from cte
pivot
(
    max(Text) for name in ([FromDate], [ToDate], [Project], [Comment])
) p
group by userid

Answer 3

Try this. I worked on your answer.

select UserId ,FromDate, ToDate, Project, Comment
from
(
  select R.UserId ,R.RText , C.ColumnName
  from [Columns] C
  INNER JOIN Response R ON C.Id=R.ColumnId
) d
pivot
(
  Min(Rtext)
  for ColumnName in (FromDate, ToDate, Project, Comment)
) piv

UNION
select UserId ,FromDate, ToDate, Project, Comment
from
(
  select R.UserId ,R.RText , C.ColumnName
  from [Columns] C
  INNER JOIN Response R ON C.Id=R.ColumnId
) d
pivot
(
  Max(Rtext)
  for ColumnName in (FromDate, ToDate, Project, Comment)
) piv;