Server将一列视为两列

Question

表看起来如下：

​

​

对于相同的RDATE和TAGNAME (如'WOJ11_%' )，应该：

​

​

对于任何帮助，非常感谢。

CREATE TABLE [dbo].[RD](
    [RDATE] [datetime] NULL,
    [TAGNAME] [nvarchar](50) NULL,
    [RVALUE] [float] NULL,
    [RMINVALUE] [float] NULL,
    [RMAXVALUE] [float] NULL
) ON [PRIMARY]

INSERT INTO [dbo].[RD]
VALUES
('2017-04-04 18:00:00.000', 'WOJ11_Q1', 0, 0, 0),
('2017-04-04 18:00:00.000', 'WOJ11_P2', 0, 0, 0),
('2017-04-04 18:00:00.000', 'WOJ11_P1', 0, 0, 0),
('2017-04-04 19:00:00.000', 'WOJ11_Q1', 65.12, 61.52, 68.52),
('2017-04-04 19:00:00.000', 'WOJ11_P2', 5.82833333333333, 5.796, 5.874),
('2017-04-04 19:00:00.000', 'WOJ11_P1', 6.18733333333333, 6.158, 6.227),
('2017-04-04 20:00:00.000', 'WOJ11_Q1', 75.72, 72.68, 77.44),
('2017-04-04 20:00:00.000', 'WOJ11_P2', 5.7345, 5.698, 5.776),
('2017-04-04 20:00:00.000', 'WOJ11_P1', 6.07925, 6.04, 6.128),
('2017-04-04 21:00:00.000', 'WOJ11_Q1', 72.2, 68.32, 79.56),
('2017-04-04 21:00:00.000', 'WOJ11_P2', 5.80575, 5.688, 5.884),
('2017-04-04 21:00:00.000', 'WOJ11_P1', 6.109, 6.021, 6.168),
('2017-04-04 22:00:00.000', 'WOJ11_Q1', 66.48, 59.68, 70.72),
('2017-04-04 22:00:00.000', 'WOJ11_P2', 5.86675, 5.766, 5.972),
('2017-04-04 22:00:00.000', 'WOJ11_P1', 6.175, 6.119, 6.236);

'WOJ11_%'的输出表(3个标记)应该如下所示：

CREATE TABLE [dbo].[RD_OUTPUT](
    [RDATE] [datetime] NULL,
    [TAGNAME_P1_RVALUE] [float] NULL,
    [TAGNAME_P1_RMINVALUE] [float] NULL,
    [TAGNAME_P1_RMAXVALUE] [float] NULL,
    [TAGNAME_P2_RVALUE] [float] NULL,
    [TAGNAME_P2_RMINVALUE] [float] NULL,
    [TAGNAME_P2_RMAXVALUE] [float] NULL,
    [TAGNAME_Q1_RVALUE] [float] NULL,
    [TAGNAME_Q1_RMINVALUE] [float] NULL,
    [TAGNAME_Q1_RMAXVALUE] [float] NULL
) ON [PRIMARY]

具有以下价值：

INSERT INTO [dbo].[RD_OUTPUT]
VALUES
('2017-04-04 18:00:00.000', 0, 0, 0, 0, 0, 0, 0, 0, 0),
('2017-04-04 19:00:00.000', 6.18733333333333, 6.158, 6.227, 5.82833333333333, 5.796, 5.874, 65.12, 61.52, 68.52),
('2017-04-04 20:00:00.000', 6.07925, 6.04, 6.128, 5.7345, 5.698, 5.776, 75.72, 72.68, 77.44),
('2017-04-04 21:00:00.000', 6.109, 6.021, 6.168, 5.80575, 5.688, 5.884, 72.2, 68.32, 79.56),
('2017-04-04 22:00:00.000', 6.175, 6.119, 6.236, 5.86675, 5.766, 5.972, 66.48, 59.68, 70.72);

Answer 1

您可以使用简单的枢轴。

select * from (
select * from #yourtable where Tagname like 'WOJ11_%' ) a
pivot (max(RValue) for TagName in ([WOJ11_P1],[WOJ11_P2],[WOJ11_Q1])) p

如果您可能需要执行动态查询来生成列并在" in“子句中提供多个标签名