主页 / dba / 问题 / 100568
Accepted
JoeNahmias
Asked: 2015-05-05 10:05:24 +0800 CST

如何在多列上执行数据透视

  • 772

我有一个数据列表，其中包括值和记录这些值的时间。我想对这些数据进行透视，以便将各个类型的观察结果和进行它们的日期/时间作为列。

这是用于创建一些示例数据的 SQL：

DECLARE @Source TABLE (
  id smallint identity primary key,
  grp varchar(3) not null,
  k varchar(10) not null,
  ts datetime not null,
  val varchar(25)
);
INSERT INTO @Source (grp, k, ts, val) VALUES
('HTZ', 'color', '2014-11-19 14:22:57.633', 'Orange'),
('HTZ', 'shape', '2014-07-23 18:31:51.797', 'Cone'),
('HTZ', 'subst', '2014-01-12 04:09:15.300', 'Canvas'),
('KTU', 'color', '2014-03-08 09:16:47.450', 'Yellow'),
('KTU', 'shape', '2014-01-27 03:51:11.810', 'Octagon'),
('KTU', 'subst', '2014-06-01 20:43:22.577', 'Granite'),
('QXR', 'color', '2014-08-13 05:18:21.917', 'Pink'),
('QXR', 'shape', '2014-09-02 12:27:13.233', 'Ovoid'),
('QXR', 'subst', '2014-08-21 16:52:32.067', 'Steel'),
('WOR', 'color', '2014-09-07 01:43:55.723', 'Teal'),
('WOR', 'shape', '2014-03-14 22:04:23.680', 'Square'),
('WOR', 'subst', '2014-04-06 04:36:28.167', 'Plastic');

这就是我想要实现的目标：

grp  color    shape    subst    color_ts                shape_ts                subst_ts
---- -------- -------- -------- ----------------------- ----------------------- -----------------------
HTZ  Orange   Cone     Canvas   2014-11-19 14:22:57.633 2014-07-23 18:31:51.797 2014-01-12 04:09:15.300
KTU  Yellow   Octagon  Granite  2014-03-08 09:16:47.450 2014-01-27 03:51:11.810 2014-06-01 20:43:22.577
QXR  Pink     Ovoid    Steel    2014-08-13 05:18:21.917 2014-09-02 12:27:13.233 2014-08-21 16:52:32.067
WOR  Teal     Square   Plastic  2014-09-07 01:43:55.723 2014-03-14 22:04:23.680 2014-04-06 04:36:28.167

我尝试了以下查询：

;WITH data AS (
  SELECT grp, k, k_ts = k + '_ts', ts, val
  FROM @Source
)
SELECT *
FROM data
PIVOT ( MAX(val) FOR k IN ([color], [shape], [subst]) ) p1
PIVOT ( MAX(ts) FOR k_ts IN ([color_ts], [shape_ts], [subst_ts]) ) p2

但是，我最终得到每个 grp+k 一行，而不是每个 grp 一行。我错过了什么？

sql-server pivot

1 个回答

  1. Best Answer

    所以这就是我所做的。我将您拥有的内容放在临时表中，然后将结果分组如下：

    ;WITH data AS (
  SELECT grp, k, k_ts = k + '_ts', ts, val
  FROM @Source

)
SELECT *
INTO #tmp
FROM data
PIVOT ( MAX(val) FOR k IN ([color], [shape], [subst]) ) p1
PIVOT ( MAX(ts) FOR k_ts IN ([color_ts], [shape_ts], [subst_ts]) ) p2

Select 
    grp
    , max(color) as color
    , max(shape) as shape
    , max(subst) as subst
    , max(color_ts) as color_ts
    , max(shape_ts) as shape_ts
    , max(subst_ts) as subst_ts
from #tmp
group by grp

    它不漂亮，但它有效。

    • 1

相关问题