旋转表引发错误："only simple column names allowed here"

Question

我有一个数据集，例如：

Master表：

x------x----------x-----------------x-----------x--------x
| Chasisnumber   |    Messagename |   PaintML | Result
x------x--------------------x-------x-----------x--------x
| A123           |   Message1       |   10    |   OK
| A123           |   Message2       |   70    |   NOK 
  B123           |   Message1       |   10    |   OK
  B123           |   Message2       |   50    |   OK
x------x--------------------x-------x-----------x--------x

我想得到：

Chasisnumber, PaintML-Message1 ,Result-Message1,PaintML-Message2,Result-Message2
| A123           | 10    |   OK  | 70    |   NOK
  B123           | 10    |   OK  | 50    |   OK

这可以通过枢轴来完成。有人可以帮助我吗？

例：

select *
from 
    (Select chasis, message, paint, result 
     from paint_b) src
pivot
(
  src.Paint, src.result for src.MessageName in 
  ('Message1',
  'Message2')
) piv;

Answer 1

就像我提到的，交叉表似乎是一个更好的主意：

--Sample Data
WITH VTE AS(
    SELECT V.Chasisnumber,
           V.Messagename,
           V.PaintML,
           V.Result
    FROM (VALUES('A123','Message1',10,'OK'),
                ('A123','Message2',70,'NOK'), 
                ('B123','Message1',10,'OK'),
                ('B123','Message2',50,'OK')) V(Chasisnumber,Messagename,PaintML,Result))
--Solution
SELECT V.Chasisnumber,
       MAX(CASE V.Messagename WHEN 'Message1' THEN V.PaintML END) AS PaintML1,
       MAX(CASE V.Messagename WHEN 'Message2' THEN V.PaintML END) AS PaintML2,
       MAX(CASE V.Messagename WHEN 'Message1' THEN V.Result END) AS Result1,
       MAX(CASE V.Messagename WHEN 'Message2' THEN V.Result END) AS Result2
FROM VTE V
GROUP BY V.Chasisnumber;

Answer 2

以下查询应执行所需的操作：

create table #tmp (Chasisnumber VARCHAR(10),Messagename VARCHAR(25), PaintML INT,Result VARCHAR(10))
insert into #tmp values
('A123','Message1',10,'OK'),
('A123','Message2',70,'NOK'), 
('B123','Message1',10,'OK'),
('B123','Message2',50,'OK')
select * from (
select Chasisnumber, unpiv.val, unpiv.col+'-'+unpiv.Messagename as col
from (select Chasisnumber,Messagename,cast(PaintML as varchar(10)) PaintML,Result from #tmp) tmp
unpivot (val for col in (PaintML,Result)) unpiv )a
pivot (max(val) for col in ([PaintML-Message1],[Result-Message1],[PaintML-Message2],[Result-Message2])) piv