sql - 对齐偏移数据值sql join

Question

目前，我使用 case 语句将两行数据转换为两列。数据未对齐。在案例陈述中，有没有办法对齐？A 列中的所有值在 B 列中都有对应的值。

+----------+----------+
| Column A | Column B |
+----------+----------+
| Null     | 0        |
+----------+----------+
| 40       | Null     |
+----------+----------+
| Null     | 0        |
+----------+----------+
| 50       | Null     |
+----------+----------+

预期输出：

+----------+----------+
| Column A | Column B |
+----------+----------+
| 40       | 0        |
+----------+----------+
| 50       | 0        |
+----------+----------+

SELECT (CASE WHEN t.[column A] = 'Column A' THEN t.value END) AS [Column A],
       (CASE WHEN t.[column B] = 'Column B' THEN t.value END) AS [Column B]
FROM t INNER JOIN t1 ON t.ID = t1.ID
WHERE t1.string = '123455'

标签： sqlsql-server-2008

Answer 1

cross apply应该满足这个要求，因为没有条件。

select t.colA, max(t.ColB) from(
   select t1.colA, t2.colB from testA t1
   cross apply testA t2
   where t1.colA is not null) t
 group by t.colA

sql小提琴