如何在Sql中将行转换为列

Question

如何在Sql中将行转换为列

sqlsql-serversql-server-2008t-sql

3

我有一个表格Columns

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

现在我想创建一个 SQL 视图，其结果应该如下所示。

我尝试使用枢轴

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;

但对我没有用，我还参考了此在 SQL Server 中高效地将行转换为列，但无法实现。你有什么想法如何在 SQL 视图中实现相同的功能吗？

表脚本：

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)

- Sid M

展示你遇到的问题及解决方法。 - SS_DBA

1

你能展示一下你尝试使用的透视查询吗？ - SS_DBA

@WEI_DBA：共享的 - Sid M

当一个用户ID在多个项目中时会发生什么？ - DForck42

@DForck42：就像我在问题中提到的期望输出，每个对应“UserId”的记录都不应该有重复记录。 - Sid M

3个回答

0

试试这个。我根据你的答案进行了修改。

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;

- SS_DBA

max和min函数对于日期类型的值返回null，这里针对的是FromDate和ToDate列。此外，您只考虑了UserId=2的两行数据，而对于任何UserId可能会有更多的行数据。 - Sid M

真的。我是根据你的数据进行操作的。 - SS_DBA

-1

你可以这样查询

;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

- Kannan Kandasamy

答案中有两个问题，1：max函数对于date类型的值返回null，在这里是针对FromDate和ToDate列。2：仅返回了一个UserId=2的行。 - Sid M

请提供您的输入样本数据脚本。 - Kannan Kandasamy

共享了这些脚本 - Sid M

根据您提供的脚本，更新了答案。对我来说运行良好。 - Kannan Kandasamy

我没有得到项目 p2 的结果, 你真的执行了你在答案中提到的查询还是只是编辑并在这里发布的？附注：我没有对你的回答进行投票。 - Sid M

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- DForck42 · Accepted Answer

坦白说，如果列类型不会改变，或者您只需要它们的子集，您可以通过过滤它们然后连接它们来代替编写一个数据透视表。我用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