sql - 如何旋转表格的几列并获得所需格式的结果。？

Question

如何旋转下表以达到所需的结果。txtItem 值不是固定的，可以有 n 个 txtItem 值。

txtItem  Rate  Revenue  Cost

   A      1200    45      30

   B      1300    55      40

   C      1400    60      30

   D      2100    55      35

预期结果：

  ColumnName      A       B      C      D

  Rate          1200    1300   1400   2100

  Revenue        45      55     60     55

  Cost           30      40     30     35

标签： sqlsql-serversql-server-2012pivotpivot-table

Answer 1

这是unvpivot& pivot，所以你可以这样做：

select colname, 
       max(case when txtItem = 'A' then val end) as A,
       max(case when txtItem = 'B' then val end) as B,
       max(case when txtItem = 'C' then val end) as C,
       max(case when txtItem = 'D' then val end) as D
from table t cross apply
     ( values (1, Rate, 'Rate'), (2, Revenue, 'Revenue'), (3, Cost, 'Cost') 
     ) tt(seq, val, colname)
group by seq, colname
order by seq;