另一种方式,类似于@Astander的(但使用OUTER APPLY
代替PIVOT / UNPIVOT
):
SELECT
a = MIN(CASE WHEN y.rn = 1 THEN y.val END),
b = MIN(CASE WHEN y.rn = 2 THEN y.val END),
c = MIN(CASE WHEN y.rn = 3 THEN y.val END),
d = MIN(CASE WHEN y.rn = 4 THEN y.val END),
e = MIN(CASE WHEN y.rn = 5 THEN y.val END),
f = MIN(CASE WHEN y.rn = 6 THEN y.val END)
FROM t
OUTER APPLY
( SELECT
x.val,
rn = ROW_NUMBER() OVER (ORDER BY rn)
FROM
( VALUES
(a,1), (b,2), (c,3), (d,4), (e,5), (f,6)
) x (val, rn)
WHERE x.val IS NOT NULL
) y
GROUP BY
t.tid ;
Test in SQL-Fiddle http://sqlfiddle.com/#!3/65b88b/1