SQL Server 将列转换为行 [英] SQL Server convert columns to rows

问题描述

我有一个包含当前值和先前值的 sql 表.

I have a sql table with current value and previous value.

Id  Value1  PValue1 Value2  PValue2
1   A       A       V       V1
2   B       B1      W       W1
3   C       C1      X       X

如果值发生变化，我想比较它们并显示在下表中.

I want to compare them and display in a the following table if the value has changes.

Id  Column  Value   Pvalue
1   Value2  V       V1
2   Value1  B       B1
2   Value2  W       W1
3   Value1  C       C1

是否可以在 SQL 2008 中不循环每一列?

Is it possible in SQL 2008 without looping each column?

推荐答案

您可以使用 CROSS APPLY 来取消数据透视:

You can use a CROSS APPLY to unpivot the data:

SELECT t.id,
  x.Col,
  x.Value,
  x.PValue
FROM YourTable t
CROSS APPLY 
(
    VALUES
        ('Value1', t.Value1, t.PValue1),
        ('Value2', t.Value2, t.PValue2)
) x (Col, Value, PValue)
where x.Value <> x.PValue;

请参阅SQL Fiddle with Demo.

仅仅因为我喜欢使用 pivot 函数，这里有一个使用 unpivot 和 pivot 函数来获得结果的版本:

Just because I love using the pivot function, here is a version that uses both the unpivot and the pivot functions to get the result:

select id, 
  colname,
  value,
  pvalue
from
(
  select id, 
    replace(col, 'P', '') colName,
    substring(col, 1, PatIndex('%[0-9]%', col) -1) new_col,  
    val
  from yourtable
  unpivot
  (
    val
    for col in (Value1, PValue1, Value2, PValue2)
  ) unpiv
) src
pivot
(
  max(val)
  for new_col in (Value, PValue)
) piv
where value <> pvalue
order by id

参见SQL Fiddle with Demo

这篇关于SQL Server 将列转换为行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！