SQL 查询枢轴方法帮助

Question

我真的正在努力解决这个问题，希望寻求帮助和启发可能会有所帮助。

假设我有下表...... 表 A

type      actId date        rowSort   order value   value_char  colName
------------------------------------------------------------------------------------
checking  1003   2011-12-31  2          1   44      44          Amount
checking  1003   2011-12-31  2          2   55      55          Interest
checking  1003   2011-12-31  2          3   66      66          Change
checking  1003   2011-12-31  2          4   77      77          Target
checking  1003   2011-12-31  2          5   88      88          Spread
savings   23456  2011-12-31  1          1   999     999         Amount
savings   23456  2011-12-31  1          2   888     888         Interest
savings   23456  2011-12-31  1          3   777     777         Change
savings   23456  2011-12-31  1          4   666     666         Target
savings   23456  2011-12-31  1          5   555     555         Spread

我想转置到表 b

checking  chkId date        rowSort   order chkvalue  chkValchar  colName  savings   savId  savVal  savValChar
-------------------------------------------------------------------------------------------------------------------
checking  1003   2011-12-31  2          1   44        44          Amount    savings   23456  999          999           
checking  1003   2011-12-31  2          2   55        55          Interest  savings   23456  888          888           
checking  1003   2011-12-31  2          3   66        66          Change    savings   23456  777          777
checking  1003   2011-12-31  2          4   77        77          Target    savings   23456  666          666
checking  1003   2011-12-31  2          5   88        88          Spread    savings   23456  555          555

我可以承认这超出了我目前的能力。
我相信我需要使用 rowSort（确定节省与检查）以及使用 order 列进行排序来对该表进行透视。这可能是错误的，这就是我在这里的原因。

枢轴是正确的方法吗？我假设我的枢轴是使用聚合 max(rowSort) 是正确的吗？

Answer 1

假设来自的rowSort检查等于来自savings的rowSort+1并且行通过字段值链接，这应该这样做：

SELECT DISTINCT

  a.type as checking,
  a.actId as chkId,
  a.date,
  a.rowSort+1,
  a.order,
  a.value as chkvalue,
  a.value_char as chkValchar,
  a.colName,
  b.type as 'savings',
  a.actId as savId,
  b.value as savVal,
  b.value_char as savValChar
  FROM tablea a
  INNER JOIN tablea b ON b.rowSort = a.rowSort+1 and b.value = a.value

Answer 2

根据您提出的要求，您不会对此查询使用PIVOT，而是希望将表JOIN 与其自身连接。下面的查询应该为您提供所需的记录，而无需使用 DISTINCT

select c.type as checking
    , c.actId as chkid
    , c.date
    , c.rowsort
    , c.[order]
    , c.value as chkvalue
    , c.value_char as chkValchar
    , c.colName
    , s.type as savings
    , s.actId as savId
    , s.value as savVal
    , s.value_char as savValchar
from t1 c
inner join t1 s
    on c.rowsort = s.rowsort + 1
    and c.[order] = s.[order]

请参阅 SQL 摆弄演示