sql - Sql中如何将行转列

Question

我有一个表列

和第二个表Response，其中保存了所有数据。

现在我想创建一个 SQL View ，结果应该是这样的

我尝试过使用 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;

但这对我不起作用，我也提到了这个 Efficiently convert rows to columns in sql server但无法实现。有什么想法可以在 SQL View 中实现同样的效果吗？

表格脚本:

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

老实说，如果列类型不会改变，或者您只需要其中的一个子集，您可以将它们过滤掉然后加入它们，而不是编写一个数据透视表。我使用 cte 编写了它，但它们可以很容易地成为子查询:

;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