I'm trying to retrieve output format but only one column able to bind. I'm working in Sql server 2008 R2. I'm working for last 2 days but I can't seem to bind it. I need this without using pivot and unpivot function.
我正在尝试检索输出格式,但只有一列能够绑定。我在Sql server 2008 R2中工作。我工作了2天但似乎无法绑定它。我需要这个而不使用pivot和unpivot功能。
Table:
| AAA | AA+ | AA | AA-
---------------------------------------------
A | 6.05 | 7.15 | 9.5 | 8.5
B | 3.02 | 4.58 | 6.25 | 7.25
C | 2.25 | 1.02 | 0 | 6
Output:
No | A | B | C
----------------------------
AAA | 6.05 | 3.02 | 2.25
AA+ | 4.05 | 3.04 | 1.02
AA | 3.02 | 4.05 | 0
AA- | 2.03 | 3.01 | 6
1 个解决方案
#1
0
You can use apply and conditional aggregation:
您可以使用apply和conditional aggregation:
select v.no,
max(case when t.col = 'A' then v.val end) as a,
max(case when t.col = 'B' then v.val end) as b,
max(case when t.col = 'C' then v.val end) as c
from t cross apply
(values ('AAA', AAA), ('AA+', [AA+]), ('AA', AA), ('AA-', [AA-])
) v(no, val)
group by v.no;
#1
0
You can use apply and conditional aggregation:
您可以使用apply和conditional aggregation:
select v.no,
max(case when t.col = 'A' then v.val end) as a,
max(case when t.col = 'B' then v.val end) as b,
max(case when t.col = 'C' then v.val end) as c
from t cross apply
(values ('AAA', AAA), ('AA+', [AA+]), ('AA', AA), ('AA-', [AA-])
) v(no, val)
group by v.no;